DiscountManager

Contents 1 The Problems and the Process 2 Operational Design: Coupons 2.1 Customer Experience: 2.2 Store Owner Experience: 2.3 Design Considerations: 2.4 Design Objectives: 2.4.1 Stage 1 2.4.2 Stage 2 2.4.3 Stage 3 3 The Design: 3.1 Data Management 3.1.1 Coupon Data System 3.1.1.1 SQL Diagrams 3.1.1.2 SQL Code 3.1.1.3 Associated System Design Needs 3.2 Code Structure 3.2.1 Admin 3.2.1.1 New Code 3.2.1.2 Code Modification 3.2.2 Catalog 3.2.2.1 New Code 3.2.2.2 Code Modifications

[edit] The Problems and the Process

The current Eos coupon system is based on Ian Wilson's original Credit Class with Gift Vouchers contribution to osCommerce with miscellaneous additions by other community developers. Welcome as it was, the design is now fairly aged, and limited by the current data structures. A number of feature requests exist, and dissatisfaction has been made public by a number of CRE Loaded forum members. The system functions, but there is a broad consensus in the community that improvement here is a high priority for many store owners.

This presents as the perfect opportunity to serve as the basis of our first public community design program. The documentation below was developed following informal discussions between myself and Charles Williams of Chain Reaction Ecommerce, Inc. Charles put our thoughts into a rather nice Word document, and I have prepared a number of data structure diagrams to accompany them. The initial document will serve as a basis for discussions which will be held in the Eos forums at this link [1].

[edit] Operational Design: Coupons

[edit] Customer Experience:

In situations where coupons are offered at an online store, the customer has certain expectation as to the use and function f the coupon.

The customer looks for an easy way to enter the coupon code and receive feedback if it is acceptable or why it is not accepted.

The customer is looking for a clear indication as to the impact the coupon has on the value of the transaction they are considering.

The customer needs the option to use or to reconsider using the coupon on the current transaction.

The case of payment failure or cart abandonment, the coupon is still valid and active should they reconsider using it on a future transaction.

The customer would like to see a list of any active coupon that have been issued to them.

[edit] Store Owner Experience:

Coupons offer the store owner and additional option to provide incentive to the customer to complete a transaction. This can range from an incentive to a new customer, to a special promotion, to a reward for long time customers. The store owner however needs tight control over what coupons are issued and of the conditions in which they may be redeemed.

Foremost, the store owner needs various types of coupons to meet the needs of their customers and the needs of the store. The options include but are not limited to things such as:

· Amount Off – These are fixed amounts that are deducted from the order.

· Percentage Off – These are percentage of the order total that is deducted.

· Free Shipping – These deduct the amount of the shipping on the order.

The store owner may need to restrict the coupon in several ways. Restrictions include but are not limited to:

· Minimum Order – The coupon can only be used on orders greater than this value.

· Uses per Coupon – This restriction limit the total number of time a coupon can be used. For example, limited to the first 100 purchases.

· Uses per Person – This restriction say how many times a single customer may use the coupon.

· Start / End Dates – Defines when the coupon can be used.

· Products / Categories Validation List – The coupon can be restricted by either products or categories, products overriding categories. When used, the order must contain one or more of the identified products.

Taxation is an important issue for the store owner. How taxation affects the final value of the coupon may change from location to location. The type of the coupon could also impact how taxes are handled on a given coupon. Therefore, taxation on coupons is not a global issue, but must be handle on a case by case base.

The store owner needs various reports to determine what coupons are active, how many have been redeemed, etc.

When a coupon is sent to a customer, it reflects on the store and therefore the presentation is important. The store owner needs options to control and enhance the coupon presentation similar to how they control and enhance the presentation of the store.

The store owner would like to attach events to the redemption of particular coupons - including customized thank you and/or survey forms.

[edit] Design Considerations:

The initial design allows for several types of coupon redemption logic, the program design should allow an easy way to add new types of redemption processing.

The coupon entry boxes allow for a coupon code to be entered. However, once a coupon has been entered, they should report the current status of the coupon. Example Images:

Coupon code and any logic related to them is held in a session. Nothing is committed to the database until the order has been confirmed. If the session is lost, then the coupon information will have to be re-entered should the customer return to the cart.

The system should be language compliant throughout, using database components as needed.

Shipping discounts may be more complex than the usual all or nothing approach currently taken. Shipping may or may not be taxable, and such taxes may or may not be legally paid by the store owner. This is driven by regulatory mandates which need to be attached to zones. Dependencies include the zone system and the shipping class which needs to provide mechanisms for dealing with these discounts that may be used by each shipping module in an appropriate fashion.

Taxation issues are also driven by regulatory mandates which are related to zones. These issues may not be addressed in isolation and are therefore NOT suitable for management entirely within this design. Identified dependencies are the Zone System and the Tax system - changes in the checkout process and other classes may be necessary as well.

[edit] Design Objectives:

Discount Manager is intended to meet each expectation outline in the Store Owner and Customer experience sections of this document. Implementation will be staged as follows:

[edit] Stage 1

1. Customer Expectation Objectives
   1. Coupon Entry
      1. The customer will be offered the opportunity to enter a coupon code early in the shopping experience on the shopping cart page and consistently thereafter.
      2. Each coupon entry will be processed for validity and an appropriate message returned for each case.
   2. Transaction Feedback
      1. Each Coupon entry will trigger a recalculation of the expected transaction and present it to the customer via:
         • the shopping cart page
         • shopping cart infobox
         • and each pertinent checkout page.
   3. Transaction Changes
      1. On each line in which coupons are presented as part of a shopping cart display, a link will be offered which allows the coupon to be removed from the transaction.
      2. Execution of these links should be the ONLY method which will remove a coupon from the transaction process from the browser side.
   4. Coupon Availability and Execution
      1. Coupons will be stored only in temporary data structures during the checkout process
      2. Coupons will not be marked as executed in the database until checkout completion.
2. Store Owner Expectation Objectives
   1. Coupon Types
      1. We will implement Fixed Amount coupons.
      2. We will implement Percentage Off coupons.
      3. We will implement global free shipping (shipping is entirely free or fully charged) in this release.
   2. Coupon Restrictions
      1. We will implement Uses per Coupon restrictions.
      2. We will implement Uses per Customer restrictions.
      3. We will implement Start and End Dates per coupon as a range restriction.
      4. We will implement Category restrictions per coupon and process this before the product restrictions
      5. We will implement Product restrictions per coupon.
   3. Coupon Taxation
      1. We will handle coupon taxation as a global issue in this release
   4. Coupon Tracking
      1. We will implement storage of data sufficient to enable coupon tracking in this release.
   5. Coupon Presentation
      1. We will implement a static presentation only in this release
   6. Coupon Events
      1. We will not implement Coupon Redemption Events in this release.

[edit] Stage 2

1. Customer Experience Objectives
   1. Coupon Availability Reporting
      1. Each customer will have a listing of their active coupons available in the Account History if and only if they have confirmed receipt of the coupon.
2. Store Owner Experience Objectives
   1. Coupon Types
      1. We will implement Shipping discounts per product in this release.
   2. Coupon Restrictions
      1. We will implement configurable restriction to or Limit by processing in this release
   3. Coupon Tracking
      1. We will implement basic coupon tracking reports in this release

[edit] Stage 3

1. Customer Experience Objectives
   1. Coupon Availability Reporting
      1. We will add a coupon receipt confirmation beginning in this release
2. Store Owner Experience Objectives
   1. Coupon Taxation
      1. We will implement support for per zone taxation of coupons beginning in this release

[edit] The Design:

[edit] Data Management

[edit] Coupon Data System

[edit] SQL Diagrams

Note that no connection to or mention of Gift Vouchers is seen. Gift Vouchers and Gift Certificates will be getting separate data support schemes of their own, as will store credit vouchers. This will provide a significant improvement in accounting granularity.

Also not shown is a proposed "summary table" which would hold accumulators for total value of gift certificates sold, credit vouchers issued and possibly coupons redeemed.

The table set here is possibly more complex than need be. Below is an alternative table which could replace the type specific restriction tables.

[edit] SQL Code

By using codes for the various restriction types, this single table would handle customer, customer group, product and product category restrictions. The cost would be a longer table and longer scan times.

The SQL creation statements for these tables looks like this:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `mydb`;

-- ----------------------------------------------------- -- Table `mydb`.`coupon_type` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_type` (

 `coupon_type_id` INT(3) NOT NULL AUTO_INCREMENT ,
 `coupon_type_code` CHAR(2) NOT NULL ,
 `get_price_function` VARCHAR(255) NULL DEFAULT NULL ,
 `set_price_function` VARCHAR(255) NULL DEFAULT NULL ,
 PRIMARY KEY (`coupon_type_id`) );

-- ----------------------------------------------------- -- Table `mydb`.`coupons` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupons` (

 `coupon_id` INT(11) NOT NULL AUTO_INCREMENT ,
 `coupon_type` CHAR(2) NOT NULL ,
 `coupon_code` VARCHAR(32) NOT NULL ,
 `coupon_amount` DECIMAL(8,4) NOT NULL ,
 `coupon_minimum_order` DECIMAL(8,4) NOT NULL ,
 `coupon_start_date` DATETIME NOT NULL ,
 `coupon_expire_date` DATETIME NOT NULL ,
 `coupon_publish` VARCHAR(1) NULL ,
 `uses_per_coupon` INT(5) NOT NULL ,
 `uses_per_user` INT(5) NOT NULL ,
 `restriction_products` VARCHAR(2) NULL DEFAULT NULL ,
 `restriction_categories` VARCHAR(2) NULL DEFAULT NULL ,
 `restriction_customers` VARCHAR(2) NULL DEFAULT NULL ,
 `restriction_customer_group` VARCHAR(2) NULL ,
 `coupon_active` CHAR(1) NOT NULL ,
 `date_created` DATETIME NOT NULL ,
 `date_modified` DATETIME NOT NULL ,
 `coupon_restrictions_coupon_restrictions_id` INT NOT NULL ,
 PRIMARY KEY (`coupon_id`, `coupon_restrictions_coupon_restrictions_id`) ,
 CONSTRAINT `coupon_type`
   FOREIGN KEY (`coupon_type` )
   REFERENCES `mydb`.`coupon_type` (`coupon_type_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `fk_coupons_coupon_restrictions`
   FOREIGN KEY (`coupon_restrictions_coupon_restrictions_id` )
   REFERENCES `` ()
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)

ENGINE = InnoDB;

CREATE INDEX coupon_type ON `mydb`.`coupons` (`coupon_type` ASC) ;

CREATE INDEX fk_coupons_coupon_restrictions ON `mydb`.`coupons` (`coupon_restrictions_coupon_restrictions_id` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`languages` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`languages` (

 `languages_id`  NULL );

-- ----------------------------------------------------- -- Table `mydb`.`coupons_description` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupons_description` (

 `coupon_id` INT(11) NOT NULL ,
 `language_id` INT(11) NOT NULL ,
 `coupon_name` VARCHAR(32) NOT NULL ,
 `coupon_description` TEXT NULL DEFAULT NULL ,
 CONSTRAINT ``
   FOREIGN KEY (`coupon_id` )
   REFERENCES `mydb`.`coupons` (`coupon_id` )
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT ``
   FOREIGN KEY (`language_id` )
   REFERENCES `mydb`.`languages` ()
   ON DELETE CASCADE
   ON UPDATE CASCADE)

ENGINE = InnoDB;

CREATE INDEX coupon_id ON `mydb`.`coupons_description` (`coupon_id` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`customers` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`customers` (

 `customers_id`  NULL );

-- ----------------------------------------------------- -- Table `mydb`.`coupon_email_track` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_email_track` (

 `unique_id` INT(11) NOT NULL AUTO_INCREMENT ,
 `coupon_id` INT(11) NOT NULL ,
 `customer_id_sent` INT(11) NOT NULL ,
 `sent_firstname` VARCHAR(32) NULL DEFAULT NULL ,
 `sent_lastname` VARCHAR(32) NULL DEFAULT NULL ,
 `emailed_to` VARCHAR(32) NULL DEFAULT NULL ,
 `date_sent` DATETIME NULL DEFAULT '0000-00-00 00:00:00' ,
 PRIMARY KEY (`unique_id`) ,
 CONSTRAINT ``
   FOREIGN KEY (`coupon_id` )
   REFERENCES `mydb`.`coupons` (`coupon_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT ``
   FOREIGN KEY (`customer_id_sent` )
   REFERENCES `mydb`.`customers` ()
   ON DELETE CASCADE
   ON UPDATE CASCADE)

ENGINE = InnoDB;

CREATE INDEX ON `mydb`.`coupon_email_track` (`coupon_id` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`coupon_redeem_track` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_redeem_track` (

 `coupon_redeem_tracker_id` INT(11) NOT NULL AUTO_INCREMENT ,
 `coupon_id` INT(11) NOT NULL ,
 `customer_id` INT(11) NOT NULL ,
 `redeem_date` DATETIME NOT NULL ,
 `redeem_ip` VARCHAR(32) NOT NULL ,
 `order_id` INT(11) NOT NULL ,
 PRIMARY KEY (`coupon_redeem_tracker_id`) ,
 CONSTRAINT ``
   FOREIGN KEY (`coupon_id` )
   REFERENCES `mydb`.`coupons` (`coupon_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT ``
   FOREIGN KEY (`customer_id` )
   REFERENCES `mydb`.`customers` (`customers_id` )
   ON DELETE CASCADE
   ON UPDATE CASCADE)

ENGINE = InnoDB;

CREATE INDEX ON `mydb`.`coupon_redeem_track` (`customer_id` ASC) ;

CREATE INDEX ON `mydb`.`coupon_redeem_track` (`customer_id` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`orders` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`orders` (

 `orders_id`  NULL );

-- ----------------------------------------------------- -- Table `mydb`.`coupon_types_description` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_types_description` (

 `coupon_types_description_id` INT(11) NOT NULL ,
 `coupon_type_id` INT(3) NULL ,
 `language_id` INT(11) NULL ,
 `coupon_type_name` VARCHAR(32) NULL ,
 `coupon_type_description` TEXT NULL ,
 PRIMARY KEY (`coupon_types_description_id`) ,
 CONSTRAINT `coupon_type_id`
   FOREIGN KEY (`coupon_type_id` )
   REFERENCES `mydb`.`coupon_type` ()
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `language_id`
   FOREIGN KEY (`language_id` )
   REFERENCES `mydb`.`languages` ()
   ON DELETE NO ACTION
   ON UPDATE NO ACTION);

CREATE INDEX coupon_type_id ON `mydb`.`coupon_types_description` (`coupon_type_id` ASC) ;

CREATE INDEX language_id ON `mydb`.`coupon_types_description` (`language_id` ASC) ;

CREATE INDEX coupon_type_id ON `mydb`.`coupon_types_description` (`coupon_type_id` ASC) ;

CREATE INDEX language_id ON `mydb`.`coupon_types_description` (`language_id` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`coupon_restrictions` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_restrictions` (

 `coupon_restrictions_id` INT NOT NULL ,
 `coupon_id` INT(11) NOT NULL ,
 `coupon_restriction_type` VARCHAR(256) NOT NULL ,
 `coupon_restriction_identifier` INT(11) NULL ,
 PRIMARY KEY (`coupon_restrictions_id`) ,
 CONSTRAINT `coupon_id`
   FOREIGN KEY (`coupon_id` )
   REFERENCES `mydb`.`coupons` (`coupon_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)

ENGINE = InnoDB COMMENT = 'InnoDB primarily for use of planning tools at present.\n\nThis table should allow entry of restrictions in a general manner by multiple modules.';

CREATE INDEX coupon_id ON `mydb`.`coupon_restrictions` (`coupon_id` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`coupon_restrictions_alternate` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_restrictions_alternate` (

 `coupon_restrictions_id` INT NOT NULL ,
 `coupon_id` INT(11) NOT NULL ,
 `coupon_restriction_type` VARCHAR(256) NOT NULL ,
 `coupon_restrict_from` VARCHAR(1) NULL ,
 `coupon_restriction_identifier` INT(11) NULL ,
 PRIMARY KEY (`coupon_restrictions_id`) )

ENGINE = InnoDB COMMENT = 'InnoDB primarily for use of planning tools at present.\n\nThis table should allow entry of restrictions in a general manner by multiple modules.';

-- ----------------------------------------------------- -- Table `mydb`.`coupon_products` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_products` (

 `coupon_products_id` INT(11) NOT NULL ,
 `coupon_id` INT(11) NOT NULL ,
 `products_id` INT(11) NOT NULL ,
 `products_restrict_from` VARCHAR(1) NULL ,
 PRIMARY KEY (`coupon_products_id`) ,
 CONSTRAINT `coupon_id`
   FOREIGN KEY (`coupon_id` )
   REFERENCES `mydb`.`coupons` (`coupon_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `products_id`
   FOREIGN KEY (`products_id` )
   REFERENCES `` ()
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)

ENGINE = InnoDB COMMENT = 'Relates coupons to products, either allowing or disallowing the coupon based on the restrict_from flag field.';

CREATE INDEX coupon_id ON `mydb`.`coupon_products` (`coupon_id` ASC) ;

CREATE INDEX products_id ON `mydb`.`coupon_products` (`products_id` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`categories` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`categories` (

 `categories_id` INT(11) NOT NULL ,
 PRIMARY KEY (`categories_id`) )

ENGINE = MyISAM;

-- ----------------------------------------------------- -- Table `mydb`.`coupon_categories` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_categories` (

 `coupon_categories_id` INT(11) NOT NULL ,
 `coupon_id` INT(11) NOT NULL ,
 `categories_id` INT(11) NULL ,
 `categories_restrict_from` VARCHAR(1) NULL ,
 PRIMARY KEY (`coupon_categories_id`) ,
 CONSTRAINT `categories_id`
   FOREIGN KEY ()
   REFERENCES `` ()
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `fk_coupon_categories_categories`
   FOREIGN KEY (`categories_id` )
   REFERENCES `mydb`.`categories` (`categories_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `fk_coupon_categories_coupons`
   FOREIGN KEY (`coupon_id` )
   REFERENCES `mydb`.`coupons` (`coupon_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)

ENGINE = InnoDB COMMENT = 'Relates coupons to product categories, either allowing or disallowing usage based on the state of the restrict_from flag.\n';

CREATE INDEX categories_id ON `mydb`.`coupon_categories` () ;

CREATE INDEX fk_coupon_categories_categories ON `mydb`.`coupon_categories` (`categories_id` ASC) ;

CREATE INDEX fk_coupon_categories_coupons ON `mydb`.`coupon_categories` (`coupon_id` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`coupon_customer_groups` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_customer_groups` (

 `coupon_customer_groups_id` INT(11) NOT NULL ,
 `coupon_id` INT(11) NOT NULL ,
 `customers_group` INT(11) NOT NULL ,
 `customers_group_restrict_from` VARCHAR(1) NULL ,
 PRIMARY KEY (`coupon_customer_groups_id`) ,
 CONSTRAINT `coupon_id`
   FOREIGN KEY (`coupon_id` )
   REFERENCES `mydb`.`coupons` (`coupon_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `customers_group`
   FOREIGN KEY (`customers_group` )
   REFERENCES `` ()
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)

ENGINE = InnoDB COMMENT = 'Relates coupons to customer groups, either allowing or disallowing usage based on the state of the restrict_from flag.';

CREATE INDEX coupon_id ON `mydb`.`coupon_customer_groups` (`coupon_id` ASC) ;

CREATE INDEX customers_group ON `mydb`.`coupon_customer_groups` (`customers_group` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`coupon_customers` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_customers` (

 `coupon_customers_id` INT(11) NOT NULL ,
 `coupon_id` INT(11) NOT NULL ,
 `customer_id` INT(11) NOT NULL ,
 `customer_restrict_from` VARCHAR(1) NULL ,
 PRIMARY KEY (`coupon_customers_id`) ,
 CONSTRAINT `coupon_id`
   FOREIGN KEY (`coupon_id` )
   REFERENCES `mydb`.`coupons` (`coupon_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `customer_id`
   FOREIGN KEY (`customer_id` )
   REFERENCES `` ()
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)

ENGINE = InnoDB COMMENT = 'Relates coupons and customers either allowing or disallowing use of the coupon depending on the state of the restrict_from flag.\n';

CREATE INDEX coupon_id ON `mydb`.`coupon_customers` (`coupon_id` ASC) ;

CREATE INDEX customer_id ON `mydb`.`coupon_customers` (`customer_id` ASC) ;

-- ----------------------------------------------------- -- Table `mydb`.`coupon_restrictions_alternate` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`coupon_restrictions_alternate` (

 `coupon_restrictions_id` INT NOT NULL ,
 `coupon_id` INT(11) NOT NULL ,
 `coupon_restriction_type` VARCHAR(256) NOT NULL ,
 `coupon_restrict_from` VARCHAR(1) NULL ,
 `coupon_restriction_identifier` INT(11) NULL ,
 PRIMARY KEY (`coupon_restrictions_id`) )

ENGINE = InnoDB COMMENT = 'InnoDB primarily for use of planning tools at present.\n\nThis table should allow entry of restrictions in a general manner by multiple modules.';

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[edit] Associated System Design Needs

In addition to these tables, there exists a need for supporting tables and/or data structures managed by additional code in the checkout process. These will be dealt with in a separate design document found here.

[edit] Code Structure

[edit] Admin

[edit] New Code

[edit] Code Modification

[edit] Catalog

Coupons, along with Gift Certificates, Gift Vouchers, Store Credits and Refunds constitute a separate class of objects which relate to but are not part of the shopping cart contents. These objects may affect a transaction, but are not essential to transaction execution. A separate class should be established to manage these features, and it is proposed that we call this the checkout class. In addition, modifications to existing code will have to be made in order to implement this new approach to the checkout.

[edit] New Code

The class will include a new module system - the Checkout Modules, and have a plugin system to support the various modules. Thus, we will add the following new directories to the file system:

1. /includes/modules/checkout
2. includes/plugins
3. includes/plugins/coupons

At least one new class will be constructed - includes/classes/checkout.php and possibly includes/classes/plugin.php.

This project will add the first new module to the system: includes/modules/checkout/coupons.php and the first plugins: includes/plugins/coupons/fixed_amount.php and includes/plugins/coupons/percentage_off.php

[edit] Code Modifications

Modifications will be necessary in orders.php, shopping_cart.php and other modules. We will identify those changes here.

Shopping Cart Class:

We will place modifications to the shopping cart class which:

allows input of discount instruments like coupons and routes the data to the discount manager. retrieves a discount totals structure from the discount manager.

This code should do nothing final with the data - storage to the DB should be temporary in nature. In particular, writes to COUPON_REDEEM_TRACKER should be replaced with writes to CUSTOMER_BASKET_COUPON, CUSTOMER_BASKET_GIFT_VOUCHER and similar temporary storage tables.

Order Class

The order class will need to retrieve information from the discount class, and pass it to the order total class.

Order Total Class

Order Total class modules will need to be modified to avoid duplicate calculations and other potential side effects.

In particular, the subtotal, total and tax methods should be moved into the class itself and not subject to sort order changes.