Complete order process

An order can have two possible ending scenarios:

The user decides the products he added to the order are necessary and finalizes it - the order passes into pending and becomes available for the site administrators. To implement this functionality, you will create the complete_order page, which computes the remaining stocks, forwards the order to the administrators and sends additional e-mail messages (e.g. thank you, confirmation, or if no stocks are available - but this case will be treated later on).
The user decides the order is no longer necessary and cancels it. At this point, all order details must be destroyed, as a new order may be created. Order details are stored in the order_ord and order_product_opr tables, as well as in the kt_order_id session variable. Therefore, clearing the order means deleting all records and variables. This is the clear_order page.

First you will implement the clear_order page that deletes all details of an existing order. To build this page, follow the next steps:

Open the clear_order page in Dreamweaver. A restrict access to page server behavior should be already on the page, added in the User authentication section of the tutorial.
Since the page must delete the order details, it will contain a delete transaction that will remove the order details from the order_ord table (this is where the ID, date, status and user are stored for each order).
To remove a record, apply the Delete Record Wizard from the MX Kollection tab of the Insert bar. Configure the wizard as follows:

In the Connection drop-down menu select the database connection created at the beginning of this tutorial.
In the Delete from table drop-down menu select the order_ord table, as only the main order details will be deleted at first.
The order ID is stored as the primary key of the table. Also, when adding a product for the first time, the custom code saves the order ID into the kt_order_id session variable as well. The condition needed to match the record to delete is to have the primary key equal the kt_order_id session variable.
Select Session variable in the Primary key equals drop-down menu and for the reference, enter kt_order_id.
The page to open after clearing an order is the product list so that the user can continue shopping. In the After deleting, go to text box enter the view_products page.
The completed dialog box should look like this:
Click OK to apply the delete transaction on page. Only a translator will be displayed in Dreamweaver, as the entire transaction is transparent to the end user.

At this point, when you click the clear order link, order details are removed from the order_ord table. However, this is not the only table storing information about an order: the order_product_opr table stores the list and quantity of products associated to an order. As shown in the database structure description, it contains pairs of identifiers from the product and order tables, as well as the quantity for each product.
To completely remove an order, all records stored in the order_product_opr table which relate to the order deleted at step 3 must be removed. Since the order ID is stored as a foreign key in the table, it can be viewed as a detail table of order_ord. MX Kollection 3 provides a server behavior that allows removing orphaned records (detail records without a master element): the Delete Detail Records trigger.
Apply the Delete Detail Records trigger from the Server Behaviors tab > + > MX Kollection > Form Validation. The trigger will automatically recognize the existing delete transaction and will register to it. Also, the master table and primary key are automatically filled in.
Configure the remaining options as follows:

In the Detail table drop-down menu select the order_product_opr table.
In the Detail foreign key select the table column storing the order ID in the order_product_opr table (it is the idord_opr column):
When the options are all set, click OK to add the trigger onto the page. You do not need to set any advanced options, as the trigger is by default configured to execute after the main transaction and retrieve the order ID from it.

Now, all order details stored in the database will be deleted when the clear order link is used. The only remaining proof of the order existence (which also raises a problem when creating a new order) is the ID saved in the kt_order_id session variable. The problem appears when the same user tries to initiate a new order, after clearing an existing one. Since the ID is still in the session variable, the order is considered as existing in the database, and adding products to the order will result into invalid records (an order ID that does not exist will be entered together with the product code in the order_product_opr table).
To remove this variable you will have to write some code of your own. To enter the code, add a Custom trigger to the page. You can access this server behavior from the Server Behaviors tab > + > MX Kollection > Forms. By default, the trigger will execute after the delete transaction, with a priority of 50. These properties are OK, as the variable removal must take place after the records are deleted from the database table.
In the Custom trigger dialog box's Basic tab enter the code that clears the session variable in the textarea. The code to enter is:

For the PHP_MySQL and PHP_ADODB server model:

unset($_SESSION['kt_order_id']);
For ColdFusion:

structdelete(Session,"kt_order_id");
For ASPVBScript:

Session.Contents.Remove("kt_order_id")

The custom trigger dialog box should look like the following (the screen shot was taken on PHP_MySQL so code differences will appear):
Apply the server behavior and add the code to the page by clicking OK.

The page that clears an order is now completed, as it removes all traces of the order, both from the database and from the session variables.

The page you will create next handles completing and confirming an order. The page that performs this action is complete_order. The list of actions to be performed by the complete order page should be:

For each product added to the order, the requested quantity must be checked against the available quantity. This is done by retrieving the ordered quantity and the product available quantity. If the ordered quantity is higher than the available one, than there is not enough in stock.
The order is then treated globally:

If at least one product cannot be provided in full quantity, the entire order is considered as being un-deliverable. If this happens, an e-mail message is sent to the purchaser, requesting action: either cancel the order, or wait until the entire order can be delivered. The order's status will be changed into "Waiting for acknowledgement", until the response from the user arrives.
If the desired quantity can be provided for each product, the available quantity is diminished with the amount ordered and the order's status is changed to "Pending".

The user is redirected to a thank you page, which contains a link to the product list page.

To implement this suite of actions, follow the next steps:

Open the complete_order page in Dreamweaver.
The quantity check and the e-mail sending are implemented using triggers. The order status update is implemented by using an update record transaction. A trigger can only be added to a page that already contains a transaction, so the update transaction will be the first one to implement.
Since the update operation will take place behind the scenes, without displaying any fields (the status to update to is set by the trigger that checks the quantity - either pending or waiting for acknowledgement), there is no point in using the wizard. Instead, use the Update Record Transaction server behavior. It can be found in the Server Behaviors tab > + > MX Kollection > Forms > Advanced.
The dialog box is divided into three tabs, each addressing some options that can be set for the transaction. The Basic tab is where you must define general information regarding the database, starting condition and redirect page:

In the Connection drop-down menu select the database connection created at the beginning of the tutorial.
For the table to update into, select the order_od - this is where the order status is stored.
In order to update only the current order, you must apply the update operation on the record that has the same ID as the one stored in the kt_order_id session variable.
The transaction must start automatically, so you must change the starter event to an entered value. Select this option in the drop-down menu and enter a value of choice for the reference (e.g. 1).
For the redirect page, browse to the thankyou.htm file in the site root.

In the Fields tab, you can decide which table columns will be involved in the update transaction and where each of them will take their value from.

Initially, all table columns are displayed in the grid. The only one that is needed is idsta_ord which stores the order status. To remove the other fields, select them one by one and click on the Minus (-) button on top of the grid.
For the remaining field (idsta_ord), set it to submit as numeric. In the Get value from drop-down menu select the element that provides the value to insert. Since the actual value will be determined by the quantity check trigger, set it to Entered value. Leave the Value field at its default reference (idsta_ord):

Click OK to close the dialog box and add all elements to the page. A translator will be displayed in Dreamweaver, pointing to where possible error messages will be displayed.
After the update transaction is added to the page, you can start creating the section that checks the stocks, updates available quantity and sets the order status. These operations cannot be done with MX Kollection 3 features, so once more, a little coding is needed. The code is entered by using a Custom Trigger. To add a custom trigger, go to the Server Behaviors tab >+>MX Kollection >Forms.
Before writing the actual code, switch to the Advanced tab, and set the trigger type to BEFORE and the priority to 1. This is done because the quantity check must take place first on page.
The code provided below executes the following actions:

The order details are retrieved from the database, with a INNER JOIN between the order_ord, product_prd and order_product_opr tables - in order to retrieve both the order and product details.
For each of the records retrieved by the query, the ordered quantity is checked against the available quantity in a loop. If for any of the ordered products there is not enough stock, a flag is set to prevent honoring the order.
After the loop is finished, the flag is evaluated. If TRUE (or 1), it means that there is not enough stock, so the status is set to "Waiting for acknowledgement" - code 2 - by executing a tNG procedure.
If the flag is FALSE (or 0), it means the order can be delivered. A new query updates the quantities for each of the ordered products. The new quantity equals the difference between the available and ordered ones.
Once the quantity update loop is over, the order status is set to "Pending" - code 1 - and the session variable storing the order ID is destroyed, to prevent further additions to the (now) completed order.

Note: The code that corresponds to a order state is stored in the status_sta table, and you can check it at any time. Also, if you decide to use other codes, or add new status codes, make sure you also add them to the status table.

The code to use is as follows (use the one appropriate to your server model)

For the PHP_MySQL server model:

$query = "SELECT order_product_opr.quantity_opr, product_prd.id_prd, product_prd.name_prd, product_prd.price_prd, product_prd.quantity_prd, order_product_opr.idord_opr FROM (order_product_opr INNER JOIN product_prd ON product_prd.id_prd=order_product_opr.idprd_opr) WHERE order_product_opr.idord_opr=".$_SESSION['kt_order_id'];

$result=mysql_query($query) or die(mysql_error);
$notInStock = 0;
while($row=mysql_fetch_assoc($result)) {

if ($row['quantity_opr']>$row['quantity_prd']) {
$notInStock = 1;
}
}

if ($notInStock == 1) {
$tNG->setColumnValue("idsta_ord", 2);

} else {

$query = "SELECT order_product_opr.quantity_opr, product_prd.id_prd, product_prd.name_prd, product_prd.price_prd, product_prd.quantity_prd, order_product_opr.idord_opr FROM (order_product_opr INNER JOIN product_prd ON product_prd.id_prd=order_product_opr.idprd_opr) WHERE order_product_opr.idord_opr=".$_SESSION['kt_order_id'];

$result=mysql_query($query) or die(mysql_error);

while($row2=mysql_fetch_assoc($result)) {
$newQnty = $row2['quantity_prd'] - $row2['quantity_opr'];
$query = "UPDATE product_prd SET quantity_prd = $newQnty WHERE id_prd = ".$row2['id_prd'];
$resultUpdateQnty = mysql_query($query) or die (mysql_error());
}

$tNG->setColumnValue("idsta_ord", 1);
}
session_unregister('kt_order_id');
return null;
For the PHP_ADODB server model:

$query = "SELECT order_product_opr.quantity_opr, product_prd.id_prd, product_prd.name_prd, product_prd.price_prd, product_prd.quantity_prd, order_product_opr.idord_opr FROM (order_product_opr INNER JOIN product_prd ON product_prd.id_prd=order_product_opr.idprd_opr) WHERE order_product_opr.idord_opr=".$_SESSION['kt_order_id'];
$result=$tNG->connection->Execute($query);
$notInStock = 0;
while(!$result->EOF) {
if ($result->Fields('quantity_opr') > $result->Fields('quantity_prd')) {
$notInStock = 1;
}
$result->MoveNext();
}
if ($notInStock == 1) {
$tNG->setColumnValue("idsta_ord", 2);
} else {
$query = "SELECT order_product_opr.quantity_opr, product_prd.id_prd, product_prd.name_prd,
product_prd.price_prd, product_prd.quantity_prd, order_product_opr.idord_opr FROM
(order_product_opr INNER JOIN product_prd ON product_prd.id_prd=order_product_opr.idprd_opr)
WHERE order_product_opr.idord_opr=".$_SESSION['kt_order_id'];
$result=$tNG->connection->Execute($query);
while(!$result->EOF) {
$newQnty = $result->Fields('quantity_prd') - $result->Fields('quantity_opr');
$query = "UPDATE product_prd SET quantity_prd = $newQnty WHERE id_prd = ".$result->Fields('id_prd');
$resultUpdateQnty = $tNG->connection->Execute($query);
$result->MoveNext();
}
$tNG->setColumnValue("idsta_ord", 1);
}
unset($_SESSION['kt_order_id']);
return null;
For the ColdFusion server model:
Like the previous page, the ColdFusion steps are little different than for PHP. In the Custom Trigger, add this line first:

return Request.AliasCustomTrigger(tNG);

Now close the Custom Trigger and go into the Code View in Dreamweaver. Add the following code immediately before the Custom Trigger code begins. You will recognize the custom Trigger code because it begins with this comment: //start Trigger_Custom trigger .
Here is the code to add:

<cffunction name="AliasCustomTrigger">
<cfargument name="tNG" required="true">
<cfset var query="">
<cfset var result= "">
<cfset var notInStock = 0>
<cfset var resultUpdateQnty = "">
<cfset query = "SELECT order_product_opr.quantity_opr, product_prd.id_prd, product_prd.name_prd,
product_prd.price_prd, product_prd.quantity_prd, order_product_opr.idord_opr FROM
(order_product_opr INNER JOIN product_prd ON product_prd.id_prd=order_product_opr.idprd_opr)
WHERE order_product_opr.idord_opr=" & SESSION['kt_order_id']>
<cfquery name="result" datasource="#tNG.connection#">
#PreserveSingleQuotes(query)#
</cfquery>

<cfloop query="result">
<cfif result.quantity_opr GT result.quantity_prd>
<cfset notInStock = 1>
</cfif>
</cfloop>
<cfif notInStock EQ 1>
<cfset tNG.setColumnValue("idsta_ord", 2)>
<cfelse>
<cfset query = "SELECT order_product_opr.quantity_opr, product_prd.id_prd, product_prd.name_prd,
product_prd.price_prd, product_prd.quantity_prd, order_product_opr.idord_opr FROM
(order_product_opr INNER JOIN product_prd ON product_prd.id_prd=order_product_opr.idprd_opr)
WHERE order_product_opr.idord_opr=" & SESSION['kt_order_id']>

<cfquery name="result" datasource="#tNG.connection#">
#PreserveSingleQuotes(query)#
</cfquery>
<cfloop query="result">
<cfset newQnty = result.quantity_prd - result.quantity_opr>
<cfset query = "UPDATE product_prd SET quantity_prd = $newQnty WHERE id_prd = " & result.id_prd>
<cfquery name="resultUpdateQnty" datasource="#tNG.connection#">
#PreserveSingleQuotes(query)#
</cfquery>

</cfloop>

<cfset tNG.setColumnValue("idsta_ord", 1)>
</cfif>
<cfset StructDelete(SESSION, 'kt_order_id')>
<cfset Request.KT_Null>
</cffunction>
<cfset Request.AliasCustomTrigger = AliasCustomTrigger>
<cfscript>
For the ASP VBScript server model

query = "SELECT order_product_opr.quantity_opr, product_prd.id_prd, product_prd.name_prd, product_prd.price_prd, product_prd.quantity_prd, order_product_opr.idord_opr FROM (order_product_opr INNER JOIN product_prd ON product_prd.id_prd=order_product_opr.idprd_opr) WHERE order_product_opr.idord_opr=" & Session("
kt_order_id")
Set result = tNG.connection.Execute(query)notInStock = 0
while Not result.EOF
If result("quantity_opr") > result("quantity_prd") Then
notInStock = 1
End If
result.MoveNext
Wend

If notInStock = 1 Then
tNG.setColumnValue "idsta_ord", 2
Else
query = "SELECT order_product_opr.quantity_opr, product_prd.id_prd, product_prd.name_prd, product_prd.price_prd, product_prd.quantity_prd, order_product_opr.idord_opr FROM (order_product_opr INNER JOIN product_prd ON product_prd.id_prd=order_product_opr.idprd_opr) WHERE order_product_opr.idord_opr=" & Session("kt_order_id")
Set result = tNG.connection.Execute(query)
While Not result.EOF
newQnty = result("quantity_prd") - result("quantity_opr")
query = "UPDATE product_prd SET quantity_prd = " & newQnty & " WHERE id_prd = " & result("id_prd")
tNG.connection.Execute(query)
result.Movenext
Wend

tNG.setColumnValue "idsta_ord", 1
End If

Session.Contents.Remove("kt_order_id")
Set Trigger_Custom = nothing

The completed dialog box (for PHP) should look as the image below:
To close the Custom trigger dialog box click OK.

The last step to take in order to complete the page is to add the trigger that sends an e-mail message if not enough products are available. This e-mail message will contain two links: one that allows cancelling the order - by deleting it from the database tables, and one that switches its status to acknowledged.

The pages are delete_order and acknowledge_order. These pages will delete and update a record, respectively, given the URL parameter passed from the mail message. A security issue arises, as if the primary key is passed directly, one can delete or acknowledge multiple orders, thus corrupting the database information. To ensure this does not happen, another unique identifier, that is much more difficult to guess, must be used.

One solution is to use a new table column to store a unique random key that is generated with the mail sending, and to delete it once the operation is finished. The other option at hand is to use the existing sesid_ord column, that already stores a unique and hard to guess value: the user's session identifier. The choice is up to you, both methods will work. In this tutorial the latter option was chosen as it does not involve altering the table.

The delete_order and acknowledge_order pages will be created later on. For now, the mail message will contain links to those pages, carrying the sesid_ord value as an URL parameter.

To add the trigger that sends the e-mail message, follow the instructions below:

Open the complete_order page, if necessary.
In order to use the sesid_ord column as dynamic data in the e-mail message, you must first retrieve it from the database. To do so, create a new recordset on the order_ord table, filtered by the kt_order_id session variable as shown below:
Before adding e-mail sending capabilities to the page, you must first retrieve the user's e-mail address. Since the email address is used for authentication, it is already available, as a session variable: kt_login_user:
To add the e-mail sending option, you will use the Send E-mail trigger. Apply it from Server Behaviors > + > MX Kollection > Send E-mail. Configure the user interface as shown below:

For the sender address, enter your site's administrative or sales address. You can also use the default sender (the one automatically filled in from the InterAKT Control Panel).
For the recipient's address you will have to use the InterAKT Dynamic Data to generate the mark-up code that retrieves it from the kt_login_user session variable:
For the Subject, you can use both entered text and dynamic data (e.g. display the order date next to the subject message - Requesting action for order completed on {rsOrderDetails.date_ord}).
Select the Write content option in the Body drop-down menu. For the content, you can use as well static and dynamic data - and HTML tags. The links for the two options must appear as well. An example content follows:

Due to a shortage of stocks, your order cannot be completed at this time.
<br>Please decide whether to wait for new supplies, or cancel the order.
<br>To cancel the order click <a href="http://myserver.com/delete_order.php?ordid={rsOrderDetail.sesid_ord}" >here</a>
<br>To wait for a new transport click <a href="http://myserver.com/acknowledge_order.php?ordid={rsOrderDetail.sesid_ord}" >here</a>
The links in the mail body above will pass the ord_id URL parameter (you can name the parameter any way you want, as long as you use the same name in the page that use them) which contains the order's associated session ID from the order_ord table.

In order for the links to be displayed correctly, you must also set the message type to HTML instead of plain text. To do this, switch to the Options tab and select the HTML text option.
The last step to perform when creating the e-mail is to make its execution conditional. Do not close the Send E-mail user interface just yet. Before starting to work with the Send E-mail trigger, it has been mentioned that it must be executed only when there is not enough stock to satisfy the order.
The existence of enough stock is reflected by the order status, after the transaction completes (its status is updated to either 2 - waiting for acknowledgment or 1 - pending). The Send E-mail trigger is by default of the AFTER type, and is executed after the transaction completes, and the status is updated in the transaction field. This will be the condition to check before starting the e-mail trigger.
To add a starting condition to the e-mail trigger switch to the Advanced tab of the user interface. In the lower part of the window, a text field labeled Condition and a Build condition button are displayed. Click on the Build condition button to open the Condition builder.
In the dialog box that opens, you can select the two expressions to compare, as well as the operator between them. For each you can use static and dynamic data.
For the first expression, click on the InterAKT Dynamic Data icon. In the new dialog box, select the idsta_ord transaction field (it is the one used in the update transaction, that now stores the new status):
Click OK to close the window and place the generated mark-up code into the first expression's text field.
Next comes the operator. The condition states that the order status must be equal to the value 2 (waiting) to send the e-mail. Therefore, select the equal sign (==) from the drop-down menu.
For the second expression, you will use a static, entered value. Since the status must be waiting for acknowledgment, the status code must equal 2. Enter the value 2 in the text field.
At this point, the trigger's starting condition is completely defined (both expressions and operator) so you can click OK to close the dialog box and return to the Send E-mail trigger user interface. The condition you defined will be also displayed in the trigger's Condition text field.

To apply the Send E-mail server behavior click on the OK button. This will close the dialog box and add a new server behavior to the page. The order can now be completed.
In the next section of the tutorial, you will create the pages that process the user's answer to the low stock e-mail message: pages that allow canceling or acknowledging the order.