|
The functions provided on the "CurrentOrder" page are
| 1. | Displaying the content of the order which is being prepared. |
| 2. | Setting the customer, the sales person and the date of order |
| 3. | Changing the quantities of the products included in the order |
| 4. | Deleting unwanted products from the order |
| 5. | Submitting the order
So now, lets start with developing this page. |
Steps:
| 1. | Create a new page and name it "CurrentOrder". |
| 2. | Place "Tutorial > CurrentOrder > Title" element into the upper part of the page. |
| 3. | Place a "zpro > ProcessForm" element below the title. |
| 4. | Place "Tutorial > CurrentOrder > OrderBlock" element into the upper part of the "ProcessForm".
The "OrderBlock" contains two "zpro > ProcessForm > ProcessBlock > ComboBox" elements (CUSTOMER_ID, EMPLOYEE_ID) and a "mor > DateNum > DateDialog" element (ORDER_DATE). |
The "OrderBlock" contains two data source elements; "customers" and "employees" that fill the options of CUSTOMER_ID and EMPLOYEE_ID combo boxes. The properties of these two elements are as follows:
The properties of "customers" data source element:
Name
|
customers
|
Connection Name
|
SuperStore
|
Query
|
SELECT * FROM customers
|
The properties of "employees" data source element:
Name
|
employees
|
Connection Name
|
SuperStore
|
Query
|
SELECT * FROM employees
|
| 1. | To inspect how the options of CUSTOMER_ID and EMPLOYEE_ID combo boxes are defined, right click on them and select "Bind Options To..." on the context menu. |
| 2. | Place "Tutorial > CurrentOrder > CartProducts" element in the "ProcessForm" below the "OrderBlock". |
"CartProducts" element contains a table that consists of a table header and a process block under it that repeats for each product existing in the shopping cart. The element also contains "cart_products" data source element whose properties are as follows:
Name
|
cart_products
|
Connection Name
|
SuperStore
|
Query
|
SELECT
c.ID, c.PRODUCT_ID, c.QUANTITY,
p.NAME, p.PRICE,
p.PRICE * c.QUANTITY as LINETOT
FROM
cart c, products p
WHERE
c.PRODUCT_ID = p.ID
|
| 3. | Place "Tutorial > CurrentOrder > SumLine" element in the "ProcessForm" below the "CartProducts". |
The text element on the right contains "$vof(sum(/cart_products/item/LINETOT))" definition. See how the summary of the LINETOT values of the cart_products data source is calculated with XSL function "sum".
"OrderBlock" Processes
| 1. | Place "mor > RelDB > RelDBUpdateProcess" element into the "OrderBlock" and set its properties as follows. |
Name
|
InsertTo_orders_Table
|
Connection Name
|
SuperStore
|
Update Query (SQL)
|
INSERT INTO orders
(CUSTOMER_ID, EMPLOYEE_ID, ORDER_DATE, STATUS)
VALUES (@vof(CUSTOMER_ID), @vof(EMPLOYEE_ID),
'@vof(ORDER_DATE)', 'Created');
selecT MAX(ID) AS v:ORDER_ID FROM orders;
|
When called, this process will insert a new record into the "orders" table. The @vof() functions in the SQL will be replaced with the values of the block input fields before the SQL is executed.
Please take a look to the second statement; The "selecT" word is not written so (all the letters are lowercase except the last one) by mistake. It is a convention to tell the RelDBUpdateProcess class to transfer the fields of the query result set to bring to the MoreMotion environment. The aim of this statement is to transfer the record id of the most recent inserted table record to the MoreMotion variable pool in variable ORDER_ID. This information will be used when inserting the detail records to the "orderdetails" table to establish a master/details relationship.
The Query property will be different for Oracle database. Since auto-increment columns are not supported by the Oracle we have to provide a value to the ID column by using the "orders_seq" sequence.
For Oracle
Update Query (SQL)
|
INSERT INTO orders
(ID, CUSTOMER_ID, EMPLOYEE_ID, ORDER_DATE, STATUS)
VALUES (orders_seq.NEXTVAL, @vof(CUSTOMER_ID), @vof(EMPLOYEE_ID),
'@vof(ORDER_DATE)', 'Created');
selecT MAX(ID) AS v:ORDER_ID FROM orders;
|
We need 2 processes in the "CartProductsBlock" element; one to delete the selected products from the cart and the other to transfer the products from cart to "orderdetails" table.
| 2. | Place "mor > RelDB > RelDBUpdateProcess" element into the "CartProductsBlock" and set its properties as follows: |
Name
|
Delete_Products
|
Connection Name
|
SuperStore
|
Update Query (SQL)
|
DELETE FROM cart WHERE ID = @vof(f:ID)
|
"Delete_Products" process will delete the selected products from the shopping cart. Note that the record id of the "cart" table is provided by @vof(f:ID) definition in the "DELETE" statement.
| 3. | Place "mor > RelDB > RelDBUpdateProcess" element into the "CartProductsBlock" and set its properties as follows: |
Name
|
InsertTo_orderdetails_Table
|
Connection Name
|
SuperStore
|
Update Query (SQL)
|
INSERT INTO orderdetails
(ORDER_ID, PRODUCT_ID, QUANTITY, UNIT_PRICE)
VALUES(@vof(v:ORDER_ID), @vof(PRODUCT_ID),
@vof(QUANTITY), @vof(PRICE))
|
This process will insert all the products in the "CartProductsBlock" into the "orderdetails" table. The @vof(v:ORDER_ID) definition will return the value of the pool variable "ORDER_ID". Remember that the value of this variable was set with selecT MAX(ID) AS v:ORDER_ID FROM orders; definition in the "InsertTo_orders_Table" process.
For Oracle
Update Query (SQL)
|
INSERT INTO orderdetails
(ID, ORDER_ID, PRODUCT_ID, QUANTITY, UNIT_PRICE)
VALUES(orderdetails_seq.NEXTVAL, @vof(v:ORDER_ID),
@vof(ID), @vof(QUANTITY), @vof(PRICE))
|
To finish the development of the page we will finally place two "zpro > ProcessForm > ExecuteCommand" elements into the "ProcessForm" and set their properties.
| 1. | Place a "zpro > ProcessForm > ExecuteCommand" element below the "SumLine" element on the left and set its properties as follows. |
Value
|
Delete Selected
|
Process List
|
<callProcess name="Delete_Products" if="_maxlcc = 0"
blockName="CartProductsBlock" recordFilter="_selected"/>
|
Validate Input
|
false
|
Next Page
|
%vof(PAGE_NAME)
|
| 2. | Place a "zpro > ProcessForm > ExecuteCommand" element below the "SumLine" element on the right and set its properties as follows. |
Value
|
Submit Order
|
Process List
|
<callProcess name="InsertTo_orders_Table"
if="_maxlcc = 0" blockName="OrderBlock1"/>
<callProcess name="InsertTo_orderdetails_Table"
if="_maxlcc = 0" blockName="CartProductsBlock"/>
<callProcess name="Delete_Products"
if="_maxlcc = 0" blockName="CartProductsBlock"/>
|
Validate Input
|
true
|
Next Page
|
%vof(PAGE_NAME)
|
The development of this page is complete. Press "Build and Test"[Shift+F9] on the Basic Toolbar and test it
|
