Introduce SKU for products on website

Project Description

We use Anuko content management system to host an online store. We need to add an SKU field to products and make it available in price list for easier integration with external systems.

Completion Notes

We need to modify the database structure to include an option whether a store uses SKUs and add the field to product options. Then, we'll need to update product editor and price list renderer to output the field when applicable. Therefore, our project plan may look like this:


Adding USE_SKU Field

We can add the use_sku field with the following SQL statement:

alter table eshops add use_sku char(1) default 'F';

Set it to false for all shops:

update eshops set use_sku = 'F';

Set the flag to true for one shop where it is needed (site_id = 10023 in our case).

update eshops set use_sku = 'T' where site_id = 10023;
commit;

Adding SKU Field to Product Options

We can add the sku field as so:

alter table prod_options add sku varchar(100);

Modifying Price List

Before modifying price list we need to make sure we get the use_sku value we just introduced for a site. For that, we have to modify the query_sites SQL query used to obtain website information in CMS.

update cms_config set param_value = 'select s.id as SITE_ID, s.hostname as SERVER_NAME,
 coalesce(a.server_alias, s.hostname) as SERVER_ALIAS,
  s.author_id as OWNER_ID, s.time_zone, s.locale_id,
  iif(s.status = 2, s.exp_date, null) as exp_date,
  iif(s.status = 2, s.exp_date+coalesce(cast(c.param_value as float),0), null) as SERVICE_EXPIRATION,
  s.title as SITE_TITLE, s.template_name, s.style_name,
  e.merchant_email, e.pref_currency, e.max_products,
  e.style_sheet as shop_style, e.payment_methods, e.nz_acc, e.nz_direct, e.use_options, e.use_anuko_checkout,
  e.use_discount_coupons, e.use_promo_campaigns, e.use_sku, s.params as SITE_PARAMS
from web_sites s
  left join site_aliases a on s.hostname = a.server_name
  left join eshops e on e.site_id = s.id
  left join cms_config c on c.param_name = ''grace_period''
where s.status >= 0'
where param_name = 'query_sites';

The above SQL contains e.use_sku, which we added to accomplish our goal.

Another query we have to modify is query_price_list:

update sql_statements set sql_text = 'select p.id, p.name, p.price as PRODUCT_PRICE,
po.price as OPTION_PRICE, po.sku,
po.opt_value, po.weight, d.path_name || wp.name as DIR
from products p
left join prod_options po on (po.product_id = p.id)
left join wiki_pages wp on (wp.id = p.page_id)
left join web_dirs d on (d.id = wp.dir_id)
left join web_sites s on (s.id = d.site_id)
where wp.has_shop = 1 and s.hostname = ${host}
order by d.path_name, wp.name, p.name'
where stmt_name = 'query_price_list';

Do not forget to reload configuration after modifying both queries.

To display SKU on price list page we need to modify /root/price_list.htm. Let's introduce a server JavaScript variable useSKU like so:

var useSKU = site.getProperty('USE_SKU') == 'T';

To conditionally display the SKU column header, we may use something like this:

out.print('<tr>');
if (useSKU) out.print('<th>SKU</th>');
out.print('<th>Product</th><th>Option</th><th>Weight</th><th>Price</th><th>Page</th></tr>');

And, to obtain SKU value for each option, we can use something like this during product enumeration:

var sku = products[i].get('SKU');

Modifying Product Editor

In this part of the project we modify prod.jsp, prod_chk.jsp, prod_get.jsp, and prod_upd.jsp parts in CMS to make display and edits of SKU column possible. We also have to modify site.jspf fragment to set use_sku variable for use. Here are the changes:

--- C:/Users/Nik/AppData/Local/Temp/site.jspf-revBASE.svn001.tmp.jspf	Thu Aug  6 20:36:47 2015
+++ C:/svn/internal/content/web/WEB-INF/jspf/site.jspf	Thu Feb 18 19:17:10 2016
@@ -1,7 +1,7 @@
 <sql:query var="qsite">
   select distinct w.id, w.hostname, s.pref_currency, 
      s.merchant_email, s.use_options, s.num_options, s.use_anuko_checkout, 
-     s.use_promo_campaigns, s.use_galleries, s.max_products, s.style_sheet
+     s.use_promo_campaigns, s.use_sku, s.use_galleries, s.max_products, s.style_sheet
   from web_sites w 
   left join eshops s on s.site_id = w.id
   left join site_aliases a on a.server_name = w.hostname
@@ -16,6 +16,7 @@
   <c:set var="use_options" value="${q.use_options == 'T' ? true : false}" scope="request"/>
   <c:set var="use_anuko_checkout" value="${q.use_anuko_checkout == 'T' ? true : false}" scope="request"/>
   <c:set var="use_promo_campaigns" value="${q.use_promo_campaigns == 'T' ? true : false}" scope="request"/>
+  <c:set var="use_sku" value="${q.use_sku == 'T' ? true : false}" scope="request"/>
   <c:set var="num_options" value="${q.num_options}" scope="request"/>
   <c:set var="use_galleries" value="${q.use_galleries == 'T' ? true : false}" scope="request"/>
   <c:set var="shop_max_prods" value="${q.max_products}" scope="request"/>

--- C:/Users/Nik/AppData/Local/Temp/prod.jsp-revBASE.svn001.tmp.jsp	Thu Aug  6 20:36:47 2015
+++ C:/svn/internal/content/web/WEB-INF/jspf/prod.jsp	Thu Feb 18 19:20:48 2016
@@ -209,11 +209,12 @@ function showImages() {
 <tr><td></td><td colspan="2">
 
 <table><tbody>
-<tr><th>Option value</th><th>Price</th><c:if test="${use_promo_campaigns}"><th>Promo</th></c:if><th>Weight</th></tr>
+<tr><th>Option value</th><c:if test="${use_sku}"><th>SKU</th></c:if><th>Price</th><c:if test="${use_promo_campaigns}"><th>Promo</th></c:if><th>Weight</th></tr>
 
 <c:forEach  begin="${0}" end="${num_options - 1}" step="${1}" var="i">
 <tr>
   <td><input type="text" size="15" value="<c:out value='${options[i].OPT_VALUE}'/>" name="opt${i+1}"></td>
+  <c:if test="${use_sku}"><td><input type="text" size="15" value="<c:out value='${options[i].SKU}'/>" name="sku${i+1}"></td></c:if>
   <td><input type="text" size="4" value="<fmt:formatNumber pattern="0.00" value='${options[i].PRICE}'/>" name="price${i+1}"></td>
   <c:if test="${use_promo_campaigns}"><td><input type="text" size="4" value="<fmt:formatNumber pattern="0.00" value='${options[i].PROMO_PRICE}'/>" name="promo_price${i+1}"></td></c:if>
   <td><input type="text" size="4" value="<fmt:formatNumber maxFractionDigits="4" value='${options[i].WEIGHT}'/>" name="weight${i+1}"> kgs</td>

--- C:/Users/Nik/AppData/Local/Temp/prod_chk.jsp-revBASE.svn000.tmp.jsp	Thu Aug  6 20:36:47 2015
+++ C:/svn/internal/content/web/WEB-INF/jspf/prod_chk.jsp	Thu Feb 18 19:26:53 2016
@@ -34,6 +34,7 @@
     NumberFormat wfmt = DecimalFormat.getNumberInstance(Locale.US);
     for (int i = 1; i <= 10; i++)  {
        String ov = request.getParameter("opt"+Integer.toString(i));
+       String osku = request.getParameter("sku"+Integer.toString(i));
        String op = request.getParameter("price"+Integer.toString(i));
        String opp = request.getParameter("promo_price"+Integer.toString(i));
        String ow = request.getParameter("weight"+Integer.toString(i));
@@ -40,6 +41,7 @@
        if (ov != null && ov.length() > 0) {
          java.util.Map o = new java.util.HashMap();
          o.put("OPT_VALUE", ov);
+         o.put("SKU", osku);
          try {
            if (op != null && op.length() > 0)
              o.put("PRICE", pfmt.parse(op));

--- C:/Users/Nik/AppData/Local/Temp/prod_upd.jsp-revBASE.svn000.tmp.jsp	Thu Aug  6 20:36:47 2015
+++ C:/svn/internal/content/web/WEB-INF/jspf/prod_upd.jsp	Thu Feb 18 19:29:40 2016
@@ -89,10 +89,11 @@
     <c:set var="option_id" value="${qid.rows[0].ID}" scope="request"/>
     
     <sql:update>
-      insert into prod_options (id, ord_num, product_id, opt_value, price, promo_price, weight)
-      values (?, ${i.index}, ?, ?, ?, ?, ?)
+      insert into prod_options (id, ord_num, product_id, sku, opt_value, price, promo_price, weight)
+      values (?, ${i.index}, ?, ?, ?, ?, ?, ?)
       <sql:param value="${option_id}"/>
       <sql:param value="${id}"/>
+      <sql:param value="${opt.SKU}"/>
       <sql:param value="${opt.OPT_VALUE}"/>
       <sql:param value="${opt.PRICE}"/>
       <sql:param value="${opt.PROMO_PRICE}"/>

Testing

Do some testing to see if everything is working as expected, when adding, modifying, or deleting products. Also make sure that stores that do not use SKU work as before.

Deployment

In this final stage of the project we deploy the application to production servers using the following steps:


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