betweenGo

Growing by Simon Peckham

Delete From One Table Whose Values Don’t Appear in Another Table

Sometimes you will find that you have items in a table whose values reference items in another table that no longer exist.  For example in ATG you may have orders that reference profiles that no longer exist.  This could happen if an order is for an anonymous profile that was deleted.

Here is an example of how to delete items in a table whose values reference items in another table that no longer exist, in this case ATG orders whose profiles no longer exist.

DELETE FROM dcspp_order
WHERE profile_id
NOT IN
(
  SELECT id
  FROM dps_user
);

This example does not actually work because of the dependencies on the dcspp_order table so please don’t try it.

Delete From One Table Based on Values in Another Table

Sometimes you want to delete items in one tables based on values in another table.  You can do this similarly to the previous case.

DELETE FROM dcspp_order
WHERE profile_id
IN
(
  SELECT id
  FROM dps_user
  WHERE id LIKE '6%'
);

This example also does not actually work because of the dependencies on the dcspp_order table so please don’t try it.

For further reading please see How to delete records from a SQL Server database and SQL Delete Rows Based on Another Table.

Isn’t it iconic? by Mykl Roventine

Convenience

eCommerce sites want to make their users’ experience as convenient and intuitive as possible.  One convenience found on most major eCommerce sites is remembering what the user put in his shopping cart, even if that person didn’t log in.  Therefore when the user returns to the site he will see what he left in his shopping cart.

Persistent Cart

ATG makes it relatively simple to do this by:

1. creating a profile in the repository (database) for all users that visit the website
2. automatically logging in users by cookie

Therefore if a user returns, she/he will be automatically logged in and if there were any items in his cart they will be added to the current cart.

Implementation

1. Turn on persisting anonymous profiles in the ProfileRequestServlet.

   # /atg/dynamo/servlet/dafpipeline/ProfileRequestServlet
   persistAfterLogout=true
   persistentAnonymousProfiles=true

2. Turn on auto-login by cookie and turn off auto-login by basic authentication.

   # /atg/userprofiling/CookieManager
   sendProfileCookies=true
   
   # /atg/userprofiling/ProfileRequestServlet
   verifyBasicAuthentication=false

3. Make all profile properties not required except for login and password in userProfile.xml.  Also make autoLogin true.

   <table name="dps_user">
     <property name="login" required="true" />
     <property name="password" required="true" />
     <property name="firstName" required="false" />
     <property name="lastName" required="false" />
     <property name="email" required="false" />
     <property name="autoLogin" default="true" />
   </table>

Notes

When a profile is created for an anonymous user the login and password are set to the user’s ID (i.e. the profile’s repository ID).

If you are adding this functionality to an existing up and running site you may have to modify your user tables so that there no “not null” columns except for the id, login and password columns, you can leave those as how they were.  Also you will need to set auto_login to true for all your existing users.

update dps_user set auto_login = 1;

To determine when the anonymous user was created look at the registrationDate profile property.  To determine when was the last time the anonymous user logged in look at the lastActivity profile property.  Both of these are updated by ATG’s TrackActivity scenario which is in the DSS folder.

For further reading please see Tracking Guest Users and Tracking Registered Users in the ATG 9.1 Personalization Programming Guide.

(Photo: Speed Limit 14 MPH by bredgur)

Sometimes the client will ask that the quantity of items you can add to the cart be limited to some number, say 14 like in the photo above.

Often people will implement this by putting in checks throughout the JSP.  But this is not the best solution because it is more labor intensive and you may miss something.

Another solution is to deal with the issue in the CartModifierFormHandler by extending the doAddItemsToOrder method.  Simply check the quantity of each AddCommerceItemInfo item and make sure that its quantity plus the quantity of the same item already in the cart does not go over the limit.  If it does modify the quantity in the AddCommerceItemInfo item appropriately.

Here is how I implemented this.

    @Override
    protected void doAddItemsToOrder(DynamoHttpServletRequest pRequest,
            DynamoHttpServletResponse pResponse) throws ServletException,
            IOException {

        // fetch the order
        Order order = getOrder();
        if (order == null) {
            String msg = formatUserMessage(MSG_NO_ORDER_TO_MODIFY, pRequest,
                    pResponse);
            throw new ServletException(msg);
        }

        // iterate through the add commerce item infos, making sure that adding
        // any of them will not result in a quantity greater than LIMIT
        AddCommerceItemInfo[] addCommerceItemInfos = getItems();
        for (int ii = 0; ii < addCommerceItemInfos.length; ii++) {

            // see if there is a commerce item already in the order for the next
            // add commerce item info
            AddCommerceItemInfo addCommerceItemInfo = addCommerceItemInfos[ii];
            String catalogRefId = addCommerceItemInfo.getCatalogRefId();
            CommerceItem commerceItem = findCommerceItemByCatalogRefId(order,
                    catalogRefId);
            if (commerceItem == null) {
                continue;
            }

            // check that the quantity we add won't result in a total quantity
            // greater than LIMIT
            long addQty = addCommerceItemInfo.getQuantity();
            long qty = commerceItem.getQuantity();
            if (qty >= LIMIT) {
                addCommerceItemInfo.setQuantity(0);
            } else if (qty + addQty > LIMIT) {
                long newAddQty = LIMIT - qty;
                addCommerceItemInfo.setQuantity(newAddQty);
            }
        }

        super.doAddItemsToOrder(pRequest, pResponse);
    }

    protected CommerceItem findCommerceItemByCatalogRefId(Order pOrder,
            String pCatalogRefId) {
        for (int ii = 0; ii < numCommerceItems; ii++) {
            CommerceItem commerceItem = (CommerceItem) commerceItems.get(ii);
            String catalogRefId = commerceItem.getCatalogRefId();
            if (catalogRefId.equals(pCatalogRefId))
                return commerceItem;
        }
        return null;
    }

(Photo: El Alma del Ebro by Paulo Brandão)

Recently I was creating some SKU and product items using the ACC and I decided to create them with custom ID’s.  The items were created fine but then I discovered I couldn’t add these items to the cart, I couldn’t use them in promotions, I couldn’t set up inventory for them, etc.  I continually got exceptions like these.

/atg/commerce/order/OrderManager	---	CONTAINER:atg.service.pipeline.RunProcessException: An exception was thrown from the context of the link named [setCatalogRefs].; SOURCE:atg.commerce.CommerceException: Unable to retrieve catalog reference for item with id ci90000002 and catalogRefId sku_free_01.
/atg/commerce/order/OrderManager		at atg.service.pipeline.PipelineChain.runProcess(PipelineChain.java:371)
/atg/commerce/order/OrderManager		at atg.service.pipeline.PipelineChainContext.runProcess(PipelineChainContext.java:185)
/atg/commerce/order/OrderManager		at atg.service.pipeline.PipelineManager.runProcess(PipelineManager.java:453)
/atg/commerce/order/OrderManager		at atg.commerce.order.OrderImpl.ensureContainers(OrderImpl.java:1261)
/atg/commerce/order/OrderManager		at atg.commerce.order.OrderImpl.getCommerceItems(OrderImpl.java:691)
/atg/commerce/order/OrderManager	....stack trace CROPPED
/atg/commerce/order/OrderManager	Caused by :atg.commerce.CommerceException: Unable to retrieve catalog reference for item with id ci90000002 and catalogRefId sku_free_01.
/atg/commerce/order/OrderManager		at atg.commerce.order.processor.ProcSetCatalogRefs.loadCatalogRef(ProcSetCatalogRefs.java:267)
/atg/commerce/order/OrderManager		at atg.commerce.order.processor.ProcSetCatalogRefs.runProcess(ProcSetCatalogRefs.java:121)
/atg/commerce/order/OrderManager		at atg.service.pipeline.PipelineLink.runProcess(PipelineLink.java:233)
/atg/commerce/order/OrderManager		at atg.service.pipeline.PipelineChain.runProcess(PipelineChain.java:343)
/atg/commerce/order/OrderManager		at atg.service.pipeline.PipelineChainContext.runProcess(PipelineChainContext.java:185)
/atg/commerce/order/OrderManager		at atg.service.pipeline.PipelineManager.runProcess(PipelineManager.java:453)
/atg/commerce/order/OrderManager		at atg.commerce.order.OrderImpl.ensureContainers(OrderImpl.java:1261)
/atg/commerce/order/OrderManager		at atg.commerce.order.OrderImpl.getCommerceItems(OrderImpl.java:691)
/atg/commerce/order/OrderManager	....stack trace CROPPED after 10 lines.

After pulling out my hair out for a few hours I realized the problem was because I had accidentally put a space at the end of the custom ID I had created. Too bad you can’t determine that from the stack trace.

Lesson Learned #1: Don’t put spaces in your custom ID’s, especially at the end.

Lesson Learned #2: Custom ID’s are a luxury you might want to avoid.

(Photo: Wolf portrait 3 by Tambako the Jaguar)

ProfileTools provides methods for updating a profile.  In ATG Commerce you can access the ProfileTools via the CommerceProfileTools.

Therefore to update a profile it is as simple as calling the update methods in ProfileTools.

getCommerceProfileTools().updateProperty(propertyName,
                                         property, getProfile());

getCommerceProfileTools().updateProperty(propertyTable, getProfile());

The update methods get the MutableRepositoryItem for the profile, set the property in the MutableRepositoryItem and then update the item in the ProfileRepository.

Pretty simple, eh?

On Monday, August 30 Cineplex launched the latest version of its store running on ATG 2007.1 and JBoss 4.0.5.GA.

betweenGo with Bell Canada was proud to be part of this release.  betweenGo implemented the handling of gift cards and product bundles and other eCommerce features.

Specific features we implemented for gift cards and product bundles included:

• product listing
• search listing
• display in cart and throughout checkout
• handling of taxes and shipping costs
• promotions
• inventory status
• adding gift cards to the cart (required some JavaScript magic

Other features we implemented included:

• lightbox to display after login if user’s cart has been modified to include items from their last session
• fixed order transaction issues

betweenGo was proud to be part of this enjoyable project.  If you need expert ATG consulting please contact us.

If you ever update an order outside of a transaction then the next time you update it within a transaction you will get the infamous, dreaded InvalidVersionException.

WARN  atg.commerce.order.ShoppingCartModifier
atg.commerce.order.InvalidVersionException: This order (o3830002) is out of date. Changes have been made to the order and the operation should be resubmitted. Order version 333, Repository item version 334.
   at atg.commerce.order.OrderManager.updateOrder(OrderManager.java:2557)

For example this could happen if you update your order in your JSP page.

<dsp:setvalue bean=”order.foo” value=”bar” />

To fix this problem you must always make sure to update an order within a transaction like this.

Transaction tr = null;
try {
  tr = ensureTransaction();
  synchronized (getOrder()) {
    getOrder().setFoo("bar");
    try {
      getOrderManager().updateOrder(order);
    }
    catch (Exception exc) {
      processException(exc, MSG_ERROR_UPDATE_ORDER, pRequest, pResponse);
    }
  }
}
finally {
  if (tr != null) commitTransaction(tr);
}

In some cases you might find a method is called within a transaction by another method and in other cases it is not.

public boolean handleFoo(DynamoHttpServletRequest req, DynamoHttpServletResponse res) {
  Transaction tr = null;
  try {
    tr = ensureTransaction();
    synchronized (getOrder()) {
      setFoo("bar");
      try {
        getOrderManager().updateOrder(order);
      }
      catch (Exception exc) {
        processException(exc, MSG_ERROR_UPDATE_ORDER, pRequest, pResponse);
      }
    }
    return checkFormRedirect(getSuccessUrl(), getErrorUrl(), req, res);
  }
  finally {
    if (tr != null) commitTransaction(tr);
  }
}

public void setFoo(String foo) {
  getOrder().setFoo(foo);
}

In the above example the handleFoo method properly updates the order within the transaction.  However calling the setFoo method directly will cause a problem since the order is not updated within a transaction.  To fix this you use the same pattern again to ensure the order is updated within a transaction.  It is okay to do this more than once during a request.

To debug this problem you can use Eclipse to make sure that wherever you update an order is always within a transaction. You can use the debugger to find which methods are called and/or you can use the call hierarchy to find out how methods are called.

Another way to help debug this is adding JSP code similar to the one I list below. It outputs the version of the order that the form handler has and the version that is in the repository.  If there is a difference then you know that the action you took before at some point updated the order outside of a transaction.

<dspel:getvalueof bean="ShoppingCartModifier.order.id" var="orderId" />
FORM HANDLER ORDER ID: ${orderId}<br/>
FORM HANDLER ORDER VERSION: <dsp:valueof bean="ShoppingCartModifier.order.version" /><br/>
<dsp:droplet name="/atg/dynamo/droplet/RQLQueryForEach">
  <dsp:param name="queryRQL" value="ID IN { \"${orderId}\" }"/>
  <dsp:param name="repository" value="/atg/commerce/order/OrderRepository"/>
  <dsp:param name="itemDescriptor" value="order"/>
  <dsp:oparam name="output">
    REPOSITORY ORDER VERSION: <dsp:valueof param="element.version"/><br/>
  </dsp:oparam>
</dsp:droplet>

For further reading please see Nabble – ATG Dynamo – Commerce Assist Returns and the Transaction Management section in the ATG Programming Guide.

Typically after you create a category in your catalog you then add products to the category.  The simple way to do that in ATG eCommerce is to use the ACC and add products to the child products property of the category.  However there is another way.

In the ATG Commerce Guide to Setting Up a Store documentation you can see in the Viewing the Product Catalog section that a category can have child products but also child product groups.

Child product groups are actually content groups which are described in the Creating Content Groups chapter of the ATG Personalization Guide for Business Users.

Though the documentation shows a content group composed of features you can easily create a content group using the ProductCatalog as a content source and product as a content type.

To create a content group follow the steps in the ATG documentation for Creating New Content Groups except use an item from the ProductCatalog when specifying the Content Type.  Then create the targeting rules for this Content Group.  Now you can specify this group in the Child products (group) property of a category.

Out of the box ATG eCommerce does not support product bundles.  It does support SKU bundles, i.e. a product can consist of multiple SKU’s.

To support product bundles is pretty simple to implement.

First you create a new type of product for bundles in /atg/commerce/catalog/productCatalog.xml.

<item-descriptor name="product">
 <table name="dcs_product">
  <property name="type" data-type="enumerated" default="regular">
   <attribute name="useCodeForValue" value="false"/>
   <option value="regular" code="0"/>
   <option value="bundle" code="1" />
 </property>
 </table>

Then you define your bundles product to have the new products property. This property is simply a list of products that the bundle contains, e.g. Costco organic cotton polo shirt and Nike athletic shorts.

<item-descriptor name="bundle" super-type="product" sub-type-value="bundle">
 <table name="betweengo_product_bundle" multi-column-name="seq_num"
  type="multi" id-column-names="product_bundle_id">
  <property name="products" column-name="product_id" data-type="list"
   component-item-type="product" display-name="Products" category="Bundle" />
 </table>
</item-descriptor>

A few years ago I did a more advanced version of this implementation which allowed for different numbers of products in one bundle, e.g. two different kinds of shirts and one pair of pants.  I did this using the concept of product links which is similar to SKU links that ATG supports.

If you are interested in wanting to implement a more advanced version please contact us.

I have done many eCommerce projects and am looking forward to doing more.

The most interesting eCommerce projects are the ones that are starting from scratch.  I was fortunate to be a part of several such projects including NFLShop.com, CasualMale.com and OriginalPenguin.com.  Of these the OriginalPenguin.com project was by far the most interesting because I was simultaneously the director, lead engineer, and QA.

Whenever I start a project or am consulting for one I ask the following questions to help determine the scope and range of the project.

1. What technology will be used?  J2EE?  ATG?  .Net?  PHP?  Ruby on Rails?
2. What platform?  Windows?  Linux?  Solaris?  FreeBSD?
3. How will it be hosted?  Locally?  Shared host?  Virtual host?  Exclusive host?
4. What database do you use or plan to use?
5. How many products, product categories and SKU’s do you have?
6. How will catalog administration be done?  Should it be part of the web application?  Or will you use a separate third-party application?
7. How will you manage price lists?
8. How will you keep track of inventory?
9. When an order is submitted, how will it be fulfilled?  Who does fulfillment?
10. How will you handle payments?  Payflow Pro?  Cyber Cash?  CyberSource?  PayPal?
11. How will you handle taxes?  TAXWARE?
12. What kind of security do you want?  Will everything be handled securely via SSL?  Do you already have an SSL server certificate for the site?
13. Will you require that to buy something you have to have an account?  If not will you want to still try to encourage buyers to get an account?  Will you be saving credit card numbers with the account?
14. Can buyers track their order history, order status, etc.?
15. What kind of emails do you want sent during the order/fulfillment process?
16. Will you want to implement promotions and/or coupons?
17. What kind of catalog navigation do you want?  Do you want a menu like navigation like Amazon.com?
18. Do you want the buyer to be able to search for items?
19. Do you want product comparison?