Customize CMS to Have Monthly Specials

Project Description

I use a Content Management System from Anuko. I am wanting to do monthly specials on some products. I love the coupon thing and still want to use that.

But rather than me going in to change prices on products for a month, then going back in and changing them back after the special, I'd like a custom-coded solution so that each product can have a special price from x date to x date, and then it automatically reverts.

Completion Notes

The above mentioned CMS has a discount coupon feature, which allows merchants to set up coupon codes, which may be used during checkout. Coupons work for any product in store but requires customer to have a code.

We need to design a way to mark some products as "promototional" during a merchant-specified time interval. When a purchase occurs within such interval, the designated products have discounted, or "special" price.

Project Plan

Let us start with some project planning. We have several project pieces that can be coded separately, to allow for better project estimates:

  1. Database changes, such as a new table for special campaigns, and corresponding changes to other tables (such as products), to see if a product is promoted and with which campaign.
  2. Specials editor - could be probably done as discount coupons editor, or very similarly.
  3. Product editor changes to accommodate an option to select a promotional campaign and an optional promotional price for each option.
  4. Product presentation changes to accommodate a special price where applicable.

Database Changes

Let's see if the following SQL will work for promotional campaigns table:

create table promo_campaigns(

id integer not null primary key,
site_id smallint not null references web_sites(id) on delete cascade,
name varchar(100) not null,
discount_percent numeric(18,2) not null,
campaign_start timestamp default null,
campaign_end timestamp default null,
note varchar(255) default null

);

Description of fields:


We'll also need an additional field in the products table. How about this one?

alter table products add promo_campaign_id integer references promo_campaigns(id) on delete set null;

Also, this one:

alter table prod_options add promo_price numeric(18, 2);

So far, database-related things look good. Let's see if we can proceed with other parts of this project.

Campaigns Editor

This part of this coding project is relatively straightforward. We do it similarly to discount coupon editor, which is already implemented. The effort boils down to create .htm placeholders, and .jsp pages for all required elements, and also modifying a menu for logged in user.

New menu to manage campaigns
New menu to manage campaigns


Promotional Campaigns page in CMS
Promotional Campaigns page in CMS

By this point we have completed steps 1) and 2) of our project plan, and user can create, edit, and delete promotional campaigns in the database.

Product Editor Changes

In this part of the project we introduce a dropdown in the product editor, which allows us to select one (or none) of the existing promotional campaigns.

We also introduce an additional promotional price column for options. The idea is that when promo price is defined, then it is used during a promo campaign, otherwise we use a discount percentage from the campaign settings.

In the end, our product editor looks pretty much like the existing product editor, with the above mentioned fields added. Promo price is getting stored in prod_options table in the promo_price field. Campaign id is stored in the products table.

Changes in product editor in CMS
Changes in product editor in CMS

This concludes item 3) of our project plan.

Product Presentation Changes

We have to modify a couple of areas in the application.

First, changes are needed in the product catalog presentation page to indicate that some products are on sale. Perhaps, the easiest is to cross over the regular price for promotional items and print a new price beside it. This will preserve formatting, as everything will still fit into the available space in most situations. Let's see how we can do it.

We need to modify ProductServlet so that it returns regular price as well as promotional price for items when applicable. When promotional price is not equal to regular price, then we have a product on sale.

So, when both prices are equal, we output the price exactly as before. When there is a discrepancy, we print the regular price with a style="text-decoration: line-through; and a promotional price beside it.

The important part is that we hide almost entire complexity in ProductServlet's Java code, specifically into a more complex SQL statement to obtain product options. This allows us to keep changes in CMS rendering part to a minimum.

Changes in product catalog in CMS
Changes in product catalog in CMS

When user clicks on the product, we can use the existing product presentation form without modifications, which is good. The only difference is that pricing for all options is now promotional when a product is on sale.

For better visual results, one would probably want to indicate that the product is on sale on the product page as well (not only in product catalog, as we did). Such visual improvements are beyond programming scope of this project.

You can leave a comment on this project, or post a new project for consideration.