/*
 * @author  Venky
 * @version 1.1
 *
 * Development Environment         :  Oracle9i JDeveloper 
 * Name of the Application         :  OptimizeSample.sqlj
 * Creation/Modification History   :
 *
 *     Venky       21-Oct-2001      Created
 *     Stephen     2-Apr-2003       Certified of Linux
 *
 */ 
 
package oracle.otnsamples.sqlj.optimization;
 
// import for JDBC classes
import java.sql.SQLException;

// import for SQLJ classes
import oracle.sqlj.runtime.Oracle;
import sqlj.runtime.ref.DefaultContext;

/**
 * Sample scenario 
 * This Sample illustrates optimization techniques available in Oracle9i SQLJ. 
 * This Sample illustrates Optimization techniques  such as , 
 *                                   1. Statement Caching 
 *                                   2. Parameter Size Definition 
 *                                   3. Column type definitions for a query 
 * This sample application allows the user to view details about all orders
 * and their order Items.User can also update quantity of an order item.
 * 
 * Here JDBC resource allocation is optimized by specifying Size and Type of 
 * quantities while updating. Statement caching is set and enabled , which  will 
 * increase performance. 
 * updateQuantity() method of OptimizeSample.sqlj specifies Size and Type of 
 * quantities while updating.
 * Statement caching size is set to 3 in the constructor of OptimizeSample.sqlj
 * as the sample uses only 3 statements.  
 * 
 * While compiling sqlj file the following flags should be enabled for 
 * optimization.
 * -optparams = true  
 *                   The "optparams" Enables parameter size definitions.If this
 *          flag is enabled, SQLJ will register input and output parameters 
 *            (host variables) to optimize JDBC resource allocations according to 
 *          sizes you specify. This file contains quantity field in the 
 *          order_items table whose size is specified as 8 in 
 *          updateProductDesc() method
 *
 * -optcols =true
 *                     This flag instruct the translator to determine types and 
 *          sizes of iterator or result set columns. This enables registration 
 *            of the columns with the Oracle JDBC driver when application runs, 
 *          saving round trips to Oracle9i depending on the particular driver 
 *          implementation
 *
 * The gui part of this sample is handled separately in OptimizeSampleFrame.java
 */
public class OptimizeSample{
  OptimizeSampleFrame           gui;                    // Handle gui
  private String                quantity    = null;
  long                          orderID     = 0;
  private int                   customerID  = 0;
  private String                orderDate   = null;
  private int                   cacheSize   = 3;

  /**
   *  Constructor. Initializes the frame. Statement caching size is set to 3. 
   */
  public OptimizeSample() {
    try{
      DefaultContext.setDefaultStmtCacheSize(cacheSize);
      gui = new OptimizeSampleFrame(this);
    } catch(Exception ex){
      System.out.println(" Error : Couldn't  Initialize frame: "+ex.toString());
      System.exit(1);
    }
  }

  /**
   * Main entry point for the class. Creates an object and calls the dbConnect()
   * method to establish connection to database. Invokes the 
   * displayOrders() to display orderID,customerID and Order Date from 
   * orders table
   */
  public static void main(String args[]){
    // Create an object of the Sample
    OptimizeSample optimizeSample = new OptimizeSample();
    
    // Connect to the database
    optimizeSample.dbConnect();
    optimizeSample.displayOrders();
  }

  /**
   *  Connect to the database using the connection parameters specified in the 
   *  Connection.properties file.
   */
  private void dbConnect(){
    try{
      gui.putStatus(" Connecting  to Database ....");      
      Oracle.connect(this.getClass(), "../../../../Connection.properties");
      gui.putStatus(" Connected to Database ...");
    } catch(SQLException ex){   // Trap errors 
      System.out.println("Error while Connecting to the database: "
                         + ex.toString());
      System.exit(1);
    }
  }
  
  /**
   * Dispatches the GUI events to the appropriate method, which performs
   * the required SQLJ operations. This method is invoked when event occurs
   * in the GUI (like table Selection, Button clicks etc.). This method
   * is invoked from the setupListeners section of OptimizeSampleFrame.java
   */
  public void dispatchEvent (String eventName) {
    if (eventName.equals(" Exit ")){
      exitApplication();
    } else if (eventName.equals(" Show Order Item Details ")){
      showOrderItems();
    } else if (eventName.equals(" Show Details For Update ")){
      showOrderItemsForUpdate();
    } else if (eventName.equals(" Update Quantity ")){
      updateQuantity();
    } else if (eventName.equals(" Refresh ")){
      gui.orderTableModel.clearTable();
      gui.orderTablePane.repaint();
      displayOrders();
    }
  }
 
 /**
  * This method retrieves the orderID, customerID and orderDate from
  * from orders table.The retrieved information is shown in JTable
  */

  private void displayOrders() {
    long        orderID      = 0;
    int         customerID   = 0;
    long      initialTime  = 0;
    long      finalTime    = 0;
    long      orderTotal   = 0;

    DefaultContext.setDefaultStmtCacheSize(cacheSize);

    #sql iterator objectIter (long order_ID,int customer_ID,String order_date);
    objectIter  tempIter=null;

    try {

      #sql  tempIter={select order_ID,customer_ID,to_char(order_date,'DD-MON-YYYY') 
                                                    order_date from orders};

      while(tempIter.next()){
        gui.addOrdersToJTable(new String(tempIter.order_ID()+""),
              new String(tempIter.customer_ID()+""),tempIter.order_date()   );
      }
      tempIter.close();

    } catch(SQLException ex) {          // Trap errors
      gui.putStatus("Error while Displaying all Orders: "+ex.toString());
    } finally {
      try {
        tempIter.close();
      } catch(SQLException sqe){
        gui.putStatus("Error while closing Iterator : "+sqe.toString());
      }
    } 
  }
  
  /**
   * Update the order Item details to the database.This method uses optimization 
   * technique by specifying Size and Type of Quantity for updation
   */
  private void updateQuantity() {
    int qty                   =   Integer.parseInt(gui.txtQuantity.getText());
    long orderID              =   Long.parseLong(gui.txtOrderID.getText());
    int productID           =       Integer.parseInt(gui.txtProductID.getText());


    try{

      // The following conditional loop checks whether user has updated product 
      // quantity and then does database update
      if(!(qty== Integer.parseInt(quantity))){

        // Quantity column's size and type are defined as a part of 
        // optimization. Here the size of Quantity  is specified as 8.
        #sql {update order_items set  Quantity=:qty /*(8)*/ 
                           where ORDER_ID = :orderID and product_ID=:productID};
        #sql {commit};
      }
    } catch(SQLException sqe){
      gui.putStatus("Error while Updating Quantity : "+sqe.toString());
    }
  }

  /**
   * This method shows information about a particular orderItem in an update 
   * form with a editable text box so that user can update product 
   * quantity of a particular order item
   */
  private void showOrderItemsForUpdate(){
    int selectedRow =  gui.orderItemsTable.getSelectedRow();
    if (selectedRow==-1) {
      gui.putStatus("  Select an Order from the Table ...");
      return;
    }

    String productID = 
        (String)gui.orderItemsTableModel.getValueAt(selectedRow,1);

    String qty = 
        (String)gui.orderItemsTableModel.getValueAt(selectedRow,2);

    this.quantity = qty;
    gui.setLabelText("Update Order Items for OrderID = "+orderID);
    gui.showOrderItemInfo(new String(orderID+""), productID, quantity);
  } 

  /**
   * This method returns the orderID of the selected order from JTable
   */
  int getOrderID(){
    int selectedRow =  gui.orderTable.getSelectedRow();

    // if user didn't select any order item then print instruction in 
    // status field to select an order
    if(selectedRow==-1) {
      gui.putStatus("  Select an Order from the Table ...");
      return -1;
    }
    int orderID =   Integer.parseInt((String)
                            gui.orderTableModel.getValueAt(selectedRow,0));
    return orderID;
  }

  /**
   * Shows all product details which are retrieved from database in JTable.
   */
  private void showOrderItems(){
    int selectedRow = gui.orderTable.getSelectedRow();
    orderDate = (String)gui.orderTableModel.getValueAt(selectedRow,2);
    customerID = Integer.parseInt((String)
                            gui.orderTableModel.getValueAt(selectedRow,1));
    orderID = getOrderID();                 // gets the user selected Order ID
    
    // if user didn't select any order then return without doing any operation
    if(orderID==-1)
        return;
  
    int   orderItemID       =   0;
    int   productID         =   0;
    int   quantity      =   0;

    // Iterator for selecting Order Items
    #sql iterator objectIter (int line_item_id, int product_Id,int quantity);
    objectIter  tempIter = null;

    try {
      #sql  tempIter={ SELECT line_item_id,product_Id,quantity FROM order_items 
                      WHERE Order_ID=:orderID};
      while(tempIter.next()){
        gui.addOrderItemsToJTable((tempIter.line_item_id()+""),
            (tempIter.product_Id()+""),(tempIter.quantity()+""));
      }
      gui.setLabelText(" Order Items for OrderID = "+orderID);
    } catch(SQLException ex) {          // Trap errors
      gui.putStatus("Error while Displaying Order Items: "+ex.toString());
    } finally{
      try{
        tempIter.close();
      } catch(SQLException sqe){
        gui.putStatus("Error while closing Iterator: "+sqe.toString());
      }
    }   
    gui.putStatus(" Select an Order Item...");  
  } 
 
  /**
   *  This method performs the clean up action and closes the application
   */
  private void exitApplication(){
    // if connected to database
    try {
      Oracle.close();
    } catch (SQLException ex){      // Trap errors
      gui.putStatus(" Error while Closing the connection: "+ex.toString()); 
    }
    // Close the application
    System.exit(0);
  }
}