The Products of SuperStore

We want to list the products existing in the products table of the database in a page called "Products". We will provide a filtering mechanism in the page to list only the products that belong to the selected category.

Additionally we will let the users to select one or more products from the list and add to the current order. This time we will use the library elements under the "Tutorial" Branch of the system library to speed up a little bit.

Steps:

1.	Create a new page and name it "Products".

2.	Place "Tutorial > Products > Title" element on the top of the body.

3.	Place "Tutorial > Products > ListProducts" element under the "Title".

ListProducts

The "ListProducts" is a ProcessForm element that contains a header section and a repeating ProcessBlock element which is repeated for each product returned from the database query.

4.	Inspect "Repeat \| For Each" property of the "ProductsBlock" element in the property editor. See the definition is "/products/item"

5.	Inspect the properties of the "products" data source

Name	products
Connection Name	SuperStore
Query	SELECT * FROM products

6.	Press "Build and Test"[Shift+F9] on the Basic Toolbar. The Application Studio will open internal web browser which will show "index".

7.	Click on "Products" link on the index page.

You've noticed that the all the products that exist in the database are listed on the page.

Listing the products of the selected category

Now we want to let the users to select one of the available product categories to filter the product list and display only the products under the selected category.

1.	Switch back to the "Products" page.

2.	Place the element "Tutorial > Products > SelectCategory" above the "ListProducts" element.

3.	Inspect the OnChange Event of this element and see how "cat" parameter is passed to the page display service.

4.	Modify the the query property of the "products" data source as follows:

Query

SELECT * FROM products

@doif(i:cat != '')

WHERE PCAT_ID = @vof(i:cat)

@doend()

Note that the WHERE clause that filters the products of the given category is included in the query only if the request parameter "cat" is available.

5.	Place a mor > RelDB > RelDBQueryDataSource into the page define its properties as follows.

Name	product_categories
Connection Name	SuperStore
Query	SELECT * FROM product_categories

6.	Build the project again and test it.

Adding the selected products to the current order

The "ListProducts" element is derived from a "ProcessForm" element. If our aim was just the displaying the products then we wouldn't need a ProcessForm. Since we want to let the users to add the selected products to the current order we need a ProcessForm and other process related elements.

The "ProductsBlock" element in the ProcessForm contains hidden input fields ID, NAME and PRICE. The values of these fields are provided by the "products" data source. Note that hidden input fields are required to post data to the server along with the request.

1.	Add a "mor > RelDB > RelDBUpdateProcess" element into the "ProductsBlock" element and configure it as follows.

Name

InsertToCart

Connection Name

SuperStore

Update Query

selecT COUNT(*) AS v:CNT FROM cart WHERE PRODUCT_ID = @vof(ID);

@doif(v:CNT > 0)

UPDATE cart SET

QUANTITY = QUANTITY + 1

WHERE PRODUCT_ID = @vof(ID);

@doelse()

INSERT INTO cart

(PRODUCT_ID, QUANTITY, USER_ID)

VALUES (@vof(ID), 1, 0);

@doend()

Insert new if it does not exist, Increase the QUANTITY if it does.

The purpose of this process is to add the selected products into the shopping cart. It should be kept in mind that it is executed for each process record that are selected by the user.

It first checks to see if the product being processed is already exists in the cart table. That is realized with the selecT COUNT(*) AS v:CNT FROM cart WHERE PRODUCT_ID = @vof(ID); statement.

The variable "v:CNT" will contain 0 if the product does not exists in the cart and 1 if it does. By evaluating the value of this variable either "UPDATE" or "INSERT" statement is executed.

For Oracle

The syntax of the INSERT statement is different for Oracle and it should be as follows:

Update Query

INSERT INTO cart

(ID, PRODUCT_ID, QUANTITY, USER_ID)
VALUES (cart_seq.NEXTVAL, @vof(ID), 1, 0);

Note that currently we insert 0 to the USER_ID column by now until we implement the security in the web application. In the "Completing the Web Application" topic we will modify this query to assign the user id of the current user to the USER_ID column.

2.	Add a "zpro > ProcessForm > ExecuteCommand" element into the "ListProducts" form and configure it as follows.

Name	AddToCart
Value	Add to Order
Process List	<callProcess name="InsertToCart" if="_maxlcc = 0" blockName="ProductsBlock" recordFilter="_selected"/>
Validate Input	false
Next Page	%vof(PAGE_NAME)

When the user selects one or more products on the list and clicks on this command button, the selected products will be added to the shopping cart. The products in the cart will be displayed in the "CurrentOrder" page which we will develop next.

You can test this page as it is (before developing the "CurrentOrder" page) and you can check the content of the "cart" table of the SuperStore database using a database browser.

3.	That's all what we have to do on this page. Press "Build and Test"[Shift+F9] on the Basic Toolbar and test the functions provided on the page such as selecting a category on the categories combo box and filtering the products, adding the selected products to the shopping cart.

Watch how it's done