Add products to orders

This section handles the page for adding new products to an existing order. Due to the structure of this application however, this page will also perform other tasks (like initiating the order). Creating a new order means adding elements into two database tables: the order's main properties go into the order_ord table (the ID, the date and state, as well as the session ID), while the correspondence to the products it contains is made through the order_product_opr table (which stores pairs of ID's: order - product).

When a user enters the site, after logging in, there is no existing order into which he can add the products. Therefore one must be created, that is unique and tied to the current browsing session. To achieve this, the session ID is used as a property for the order. The mechanism that takes place on the add_to_order page is as follows:

1. After a user clicks the Add to order link for a product, the add_to_order page is loaded with the product's ID as URL parameter.

2. The page code must check if an order associated to this session already exist, by verifying if an order ID is stored in the session variable kt_order_id. If found, it is the ID of the order that will be used throughout the insert operations into the order_product_opr table.

3. If an existing order is not found with the ID in the kt_order_id session variable, the session ID is retrieved, and a table search is conducted, to see if an existing order exists for this session, but is not loaded in the kt_order_id variable. If one is found, it's ID is retrieved and loaded into a session variable (the one checked at step 2) and the insert transaction carries on.

4. If no matching record is found in the database, it means that no order was ever associated to this user's session. Therefore, a new order is created by inserting into the order_ord table a new record, with the present date, the initiated state (the 0 value) and with the current user's ID. Then, the last inserted ID is also saved into a session variable.

All the actions described above take place before the actual insert operation that adds a new product to a order, and they cannot be done automatically by MX Kollection. Some custom code is necessary to achieve this set of operations. MX Kollection helps however through the use of its Custom Trigger server behavior, that allows entering code and deciding when to execute it. Start by opening the add_to_order page in Dreamweaver.

Before you can add a custom trigger on the page, you must have a transaction (either Insert, Update, Delete or Custom). Since the page's purpose is to insert products into the order, you will apply the Insert Record Form Wizard. You can access it from the MX Kollection tab of the Insert bar and must configure it as follows:

1. In the wizard's first step, select the database connection created at the beginning of this tutorial. For the table to insert into, use order_product_orp (it is the linking table, that only stores pairs of ID's and the quantity). For the redirect page, use the product list (view_products):
   
   
    

2. In the second step, you must decide what fields will be used from the existing table columns. Configure them as follows:

• The idprd_opr field is passed to the page as an URL parameter. It should not have a form field correspondent, as it will be invisible for the user. In the Display as drop-down menu select Hidden field. For the default value, click the InterAKT Dynamic Data icon and select URL parameter. Enter the name of the parameter - id_prd.
  
  
   

• The idord_opr field will take its value from a session variable (you can name the variable as you choose, as long as it is consistent throughout the tutorial - let's name it kt_order_id). Set it to Hidden field and for the default value use the InterAKT Dynamic Data again. Select the Session variable this time and for the name enter kt_order_id.

• Since the quantity is a property that can be changed by the user, leave it as a text field that submits a numeric value.
  
  
   

3. The third step of the wizard allows you to define validation rules for each field involved in the transaction. You can safely skip this step and close the wizard by pressing the Finish button.

At this point the page contains the form allowing to insert records into the database (as HTML elements) and the Insert transaction (regarding the logic). Since a transaction exists, this means you can add triggers to the page. The Custom Trigger is the most useful at this point.

To add a custom trigger to the page, access it from Server Behaviors > MX Kollection > Forms > Custom trigger. In the user interface that opens, you can enter code. The code executing the operations mentioned on top of this page is provided below:

• For the PHP_MySQL, PHP_ADODB server models:
  
  if(!isset($_SESSION['kt_order_id']))
  {
  $id = session_id();
  $query = "INSERT INTO order_ord (idusr_ord,date_ord,idsta_ord, sesid_ord) VALUES
  ('".$_SESSION['kt_login_id']."', '".date("Y-m-d",time())."',0, '$id')";
  $tNG->connection->execute($query);
  $_SESSION['kt_order_id'] = $tNG->connection->Insert_ID('order_ord', 'id_ord');
  }
  $tNG->setColumnValue("idord_opr", $_SESSION['kt_order_id']);
  return NULL;
   

• For ColdFusion:
  The ColdFusion logic is a bit different than that for PHP. First, in the Custom Trigger, add this line:
  
  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= "">
  <cfif NOT isDefined("Session.kt_order_id")>
  <cfset id = Session.CFID>
  
  <cflock name="insertOrderOrd" type="exclusive" timeout="10">
  <cfset query = "INSERT INTO order_ord (idusr_ord,date_ord,idsta_ord, sesid_ord) VALUES ('" & SESSION['kt_login_id'] & "', '" & DateFormat(NOW(),"yyyy-mm-dd") & "',0, '" & id & "')">
  <cfquery name="result" datasource="#tNG.connection#">
  #PreserveSingleQuotes(query)#
  </cfquery>
  <cfset query ="SELECT max(id_ord) as orderid FROM order_ord">
  <cfquery name="result" datasource="#tNG.connection#">
  #PreserveSingleQuotes(query)#
  </cfquery>
  <cfset SESSION['kt_order_id'] = result.orderid>
  </cflock>
  <cfset tNG.setColumnValue("idord_opr", SESSION['kt_order_id'])>
  </cfif>
  <cfreturn Request.KT_Null>
  </cffunction>
  <cfset Request.AliasCustomTrigger = AliasCustomTrigger>
   

• For ASPVbScript:
  
  If Session("kt_order_id") = "" Then
  id = Session.SessionID
  query = "INSERT INTO order_ord (idusr_ord,date_ord,idsta_ord, sesid_ord) VALUES ('" & Session("kt_login_id") &  "', '" &
  Date() & "',0, '" & id & "')"
  tNG.connection.Execute(query)
  query = "SELECT @@IDENTITY as newid"
  Set rsID = tNG.connection.Execute(query)
  pkValue = Cstr(rsID.Fields.Item("newid").Value & "")
  Session("kt_order_id") = pkValue
  tNG.setColumnValue "idord_opr", Session("kt_order_id")
  End If
  Set Trigger_Custom = nothing

Next, you have to set the trigger to be executed before the transaction. To set trigger properties, click on the Advanced tab. Here, set the Type from the drop-down menu to BEFORE and the priority to 1. This means that the trigger will execute before the transaction it is registered to (you can see it in the grid), with the highest priority.

Now, when the page loads, the existence of an order for the current session is checked, and if it does not exist, one is initialized, before the user actually adds the product and the transaction will go on.

After adding the possibility to browse the product list and add any of them to the order, together with a specified quantity, you must create the pages that allow finalizing the order. To create them, move on to the next section of the tutorial.