Liferay Shopping Cart and Payments

Here are some notes I took when I wanted to use the Shopping cart database from my own programs and processes.

This article assumes you are familiar with Rich Sezov's book 'Liferay in Action'. If not, get the book and read it before you go and redevelop both Liferay and his book. You will wan to use the existing Liferay services to access the information.

This is a very quick look at the Liferay 6.0.6 shopping cart, I will improve it as I learn and use more features.

Please also look at and contribute to the improvements WIKI

http://www.liferay.com/community/wiki/-/wiki/4038621/Liferay+as+an+e-commerce+platform/maximized

Shopping carts are unique across at least virtual servers (Admin Control Panel -> Portal Instance). I have not checked at Organization, group and community level)

I wrote this up whilst trying to integrate to the Shopping cart, to get it to process payments, manage invoices and track fulfillment. I can get round many of the shortcomings using hook replacement of processes, Liferay services and expando data. Use PortletFaces (www.portletfaces.org) give your cstomers great AJAX front ends.

Shopping catalogue

As shop owner, you set up your catalogue containing Ctaegories and items.

The facilities in Liferay are good and work well (see improvements WIKI).

/*     Predefined shopping catalogue categories set up by the site owner  e.g. membership fees, photos, films, cameras etc */
CREATE MEMORY TABLE SHOPPINGCATEGORY(CATEGORYID BIGINT NOT NULL PRIMARY KEY,

GROUPID BIGINT,
COMPANYID BIGINT,
USERID BIGINT,
USERNAME VARCHAR(75),
CREATEDATE TIMESTAMP,
MODIFIEDDATE TIMESTAMP,
PARENTCATEGORYID BIGINT,
NAME VARCHAR(75),
DESCRIPTION LONGVARCHAR)

/* Item prices in the catalogue, each may be a member of a category. */
CREATE MEMORY TABLE SHOPPINGITEM(ITEMID BIGINT NOT NULL PRIMARY KEY,

GROUPID BIGINT,
COMPANYID BIGINT,
USERID BIGINT,
USERNAME VARCHAR(75),
CREATEDATE TIMESTAMP,
MODIFIEDDATE TIMESTAMP,
CATEGORYID BIGINT,
SKU VARCHAR(75),
NAME VARCHAR(200),
DESCRIPTION LONGVARCHAR,
PROPERTIES LONGVARCHAR,
FIELDS_ BOOLEAN,
FIELDSQUANTITIES LONGVARCHAR,
MINQUANTITY INTEGER,
MAXQUANTITY INTEGER,
PRICE DOUBLE,
DISCOUNT DOUBLE,
TAXABLE BOOLEAN,
SHIPPING DOUBLE,
USESHIPPINGFORMULA BOOLEAN,
REQUIRESSHIPPING BOOLEAN,
STOCKQUANTITY INTEGER,
FEATURED_ BOOLEAN,
SALE_ BOOLEAN,
SMALLIMAGE BOOLEAN,
SMALLIMAGEID BIGINT,
SMALLIMAGEURL LONGVARCHAR,
MEDIUMIMAGE BOOLEAN,
MEDIUMIMAGEID BIGINT,
MEDIUMIMAGEURL LONGVARCHAR,
LARGEIMAGE BOOLEAN,
LARGEIMAGEID BIGINT,
LARGEIMAGEURL LONGVARCHAR)

/*   User defined (Expando) Columns on the shopping item */
CREATE MEMORY TABLE SHOPPINGITEMFIELD(ITEMFIELDID BIGINT NOT NULL PRIMARY KEY,

ITEMID BIGINT,
NAME VARCHAR(75),
VALUES_ LONGVARCHAR,
DESCRIPTION LONGVARCHAR)

/* Special prices, promotions, quantity discounts set p by shop owner */
CREATE MEMORY TABLE SHOPPINGITEMPRICE(ITEMPRICEID BIGINT NOT NULL PRIMARY KEY,

ITEMID BIGINT,
MINQUANTITY INTEGER,
MAXQUANTITY INTEGER,
PRICE DOUBLE,
DISCOUNT DOUBLE,
TAXABLE BOOLEAN,
SHIPPING DOUBLE,
USESHIPPINGFORMULA BOOLEAN,
STATUS INTEGER)

Create Stock Items in the Category
You can do this in the shopping portlet of Liferay as shop owner

INSERT INTO SHOPPINGCATEGORY VALUES(14217,10877,1,10875,'Bruno Admin','2011-08-29 04:50:53.908000000','2011-08-29 04:50:53.908000000',0,'Watches','Watches, Bracelets, etc')

INSERT INTO SHOPPINGITEM VALUES(14213,10877,1,10875,'Bruno Admin','2011-08-29 04:49:03.542000000','2011-08-29 04:53:33.320000000',14217,
'IGOLD','Gold Watch','Gold watch',
'Gols ...',FALSE,'',0,0,60.0E0,0.0E0,TRUE,0.0E0,FALSE,FALSE,0,FALSE,FALSE,FALSE,14214,'',FALSE,14215,'',FALSE,14216,'')

INSERT INTO SHOPPINGITEM VALUES(14218,10877,1,10875,'Bruno Admin','2011-08-29 04:52:59.968000000','2011-08-29 04:52:59.968000000',14217,
'SILVER','Silver Watch','Silver watch ....','silver badge, silver hat, silver tie pin',FALSE,
'',0,0,40.0E0,0.0E0,TRUE,0.0E0,FALSE,FALSE,0,FALSE,FALSE,FALSE,14219,'',FALSE,14220,'',FALSE,14221,'')

 

Customer Experience

Your customer interacts with the Shopping cart, and all items are maintained across different sessions until your customer either empties the cart, or performs a checkout action.

on Checkout, the items in the cart are converted to an Order, the customer ills in shipping and billing addresses, then goes to pay using paypal or a credit card.

/* *********************************************************************** Customer centric data */

CREATE MEMORY TABLE SHOPPINGCART(CARTID BIGINT NOT NULL PRIMARY KEY,

GROUPID BIGINT,
COMPANYID BIGINT,
USERID BIGINT,
USERNAME VARCHAR(75),
CREATEDATE TIMESTAMP,
MODIFIEDDATE TIMESTAMP,
ITEMIDS LONGVARCHAR,
COUPONCODES VARCHAR(75),
ALTSHIPPING INTEGER,
INSURE BOOLEAN)

CREATE MEMORY TABLE SHOPPINGORDER(ORDERID BIGINT NOT NULL PRIMARY KEY,

GROUPID BIGINT,
COMPANYID BIGINT,
USERID BIGINT,
USERNAME VARCHAR(75),
CREATEDATE TIMESTAMP,
MODIFIEDDATE TIMESTAMP,
NUMBER_ VARCHAR(75),
TAX DOUBLE,
SHIPPING DOUBLE,
ALTSHIPPING VARCHAR(75),
REQUIRESSHIPPING BOOLEAN,
INSURE BOOLEAN,
INSURANCE DOUBLE,
COUPONCODES VARCHAR(75),
COUPONDISCOUNT DOUBLE,
BILLINGFIRSTNAME VARCHAR(75),
BILLINGLASTNAME VARCHAR(75),
BILLINGEMAILADDRESS VARCHAR(75),
BILLINGCOMPANY VARCHAR(75),
BILLINGSTREET VARCHAR(75),
BILLINGCITY VARCHAR(75),
BILLINGSTATE VARCHAR(75),
BILLINGZIP VARCHAR(75),
BILLINGCOUNTRY VARCHAR(75),
BILLINGPHONE VARCHAR(75),
SHIPTOBILLING BOOLEAN,
SHIPPINGFIRSTNAME VARCHAR(75),
SHIPPINGLASTNAME VARCHAR(75),
SHIPPINGEMAILADDRESS VARCHAR(75),
SHIPPINGCOMPANY VARCHAR(75),
SHIPPINGSTREET VARCHAR(75),
SHIPPINGCITY VARCHAR(75),
SHIPPINGSTATE VARCHAR(75),
SHIPPINGZIP VARCHAR(75),
SHIPPINGCOUNTRY VARCHAR(75),
SHIPPINGPHONE VARCHAR(75),
CCNAME VARCHAR(75),
CCTYPE VARCHAR(75),
CCNUMBER VARCHAR(75),
CCEXPMONTH INTEGER,
CCEXPYEAR INTEGER,
CCVERNUMBER VARCHAR(75),
COMMENTS LONGVARCHAR,
PPTXNID VARCHAR(75),
PPPAYMENTSTATUS VARCHAR(75),
PPPAYMENTGROSS DOUBLE,
PPRECEIVEREMAIL VARCHAR(75),
PPPAYEREMAIL VARCHAR(75),
SENDORDEREMAIL BOOLEAN,
SENDSHIPPINGEMAIL BOOLEAN)

CREATE MEMORY TABLE SHOPPINGORDERITEM(ORDERITEMID BIGINT NOT NULL PRIMARY KEY,

ORDERID BIGINT,
ITEMID VARCHAR(75),
SKU VARCHAR(75),
NAME VARCHAR(200),
DESCRIPTION LONGVARCHAR,
PROPERTIES LONGVARCHAR,
PRICE DOUBLE,
QUANTITY INTEGER,
SHIPPEDDATE TIMESTAMP)

 

 

Purchase Cart
This is what the buyer creates as they shop on our site

The shopping cart is created for the buyer when they add the first item, it contains the items the user wants to buy.

You probably just want to access and modify the shoppingcart, then leave the remainder of the workflow intact.

E-Mail must be working since confirmations are sent via E-mail

The facilities in Liferay are good and work well (see improvements WIKI).

The entry of credit card number require no spaces between the groups, this makes it more difficult to check from a human perspective.

The shopping cart  has database lifetime

Items in the shopping cart have database lifetime and are remembered between sessions (ShoppingCart.ItemIds)
If multiple of any item is in the cart, the ItemId is repeated N times!! (longvarchar field)

History is maintained in the Orders link of the shopping portlet.

 

When we checkout the cart
       We enter company name, billing and shipping address
       We get a pro-forma invoice
       On pressing 'continue' we go to Paypal
       Only way out is browser back button

Liferay can process from here with some exceptions
        Addresses are not on file for Michelle
        No VAT details
        No Sequential VAT invoice numbering

User data remains in cart till we checkout and pay (both cart and orders)
         Status levels are checkout, completed, denied, pending, refunded

 

 

*/
    

/*     Michelle adds Gold to her cart, then logs out, on logging in again, GOLD is in her cart */
INSERT INTO SHOPPINGCART VALUES(14407,10877,1,11019,'Michelle Writer',
'2011-08-29 06:55:24.291000000',
'2011-08-29 06:55:35.181000000',
'14213,'   #   ItemIds contains CSV of the items in the cart, where are the quantities?
                        # if the quantity is 3, the CSV will read '14213,14213,14213,'
         ,'',0,FALSE)

/* Then adds a second item and checks out, fills in the checkout details and continues
          gets to paypal screen and presses browser back button
Paypal have billing adress details set up in liferay form
*/

/* This order is created when the buyer presses 'Checkout', that way, details are not lost between sessions */

INSERT INTO SHOPPINGORDER VALUES(14501,10877,1,11019,'Michelle Writer',
'2011-08-29 07:40:45.870000000','2011-08-29 07:40:45.883000000',
'7SC1KLNI21YO',0.0E0,0.0E0,'',FALSE,FALSE,0.0E0,'',0.0E0,
'Michelle','Writer','michelle@7cogs.com','Liferay Company','Liferay Street','Liferay','outside-us','Zip21','Country','Phone',TRUE,
'Michelle','Writer','michelle@7cogs.com','Liferay Company','Liferay Street','Liferay','outside-us','Zip21','Country','2711Phone','',
'','',0,0,'','My COmment','','LIFERAY_STATUS_CHECKOUT',0.0E0,'','',TRUE,TRUE)

/* These details are recorded at time of purchase, so changing the cattalogue prices does not affect the invoice */
INSERT INTO SHOPPINGORDERITEM VALUES(14506,14501,'14213','GOLD_WATCH',
'Gold ','Gold watch','Gold watch with 18 ct bracelet...',60.0E0,1,NULL)
INSERT INTO SHOPPINGORDERITEM VALUES(14507,14501,'14218','SILVER_WATCH',
'Silve','Silver necklace','silver badge, silver hat, silver tie pin',40.0E0,1,NULL)