/*
 * @author  Chandar
 * @version 1.0
 *
 * Development Environment        :  Oracle9i JDeveloper 
 * Name of the Application        :  IterSubclassSample.sqlj
 * Creation/Modification History  :
 *
 *     Chandar       8-Oct-2001      Created
 *     Stephen       1-Apr-2003      Certified on Linux
 *
 */


package oracle.otnsamples.sqlj.itersubclass;

//SQLJ related imports
import oracle.sqlj.runtime.Oracle;
import sqlj.runtime.profile.RTResultSet;

// Scrollable Iterator related import
import sqlj.runtime.Scrollable;


import java.util.Vector;

// Create a Named Iterator for order details
#sql iterator OrderIter implements Scrollable(Integer ORDER_ID ,
    String FIRST_NAME,String LAST_NAME,String ORDER_MODE , Double ORDER_TOTAL);

/**
 * Subclass the OrderIter iterator 
 */
class SubOrderIter extends OrderIter {
      
  /**
   * We must provide a constructor for sqlj.runtime.RTResultSet
   * This constructor is called in the assignment of SubOrderIter to a query.
   */
  public SubOrderIter(RTResultSet rs) throws Exception{
    super(rs); 
  }

  /**
   * This method gets the order details and commission for selected employee
   * (sales representative).
   */
  public void getOrdersAndCommission(String empName,float commPercent) 
      throws Exception { 
    float totalOrder=0;     
    float commission=0;

    // Loop through the contents of iterator to display them
    while (this.next()){


      // Add the value of order to totalOrder variable
      totalOrder=totalOrder + this.ORDER_TOTAL().floatValue();
    }

    // Calculate the commission for selected employee using total order value
    // and his commission percentage
    commission =totalOrder *commPercent;

    // display the orders, order total and commission on frame window 
    this.display(totalOrder, commission,empName);
  }

  /**
   * This method displays the orders , order total and commission for a sales
   * representative in frame window
   */
  public void display(float totalOrder,float commission,String empName){
    // Instantiate the frame class to show orders
    IterSubclassOrdersFrame orderframe = new IterSubclassOrdersFrame();
    try{  
      // Display  the frame window
      orderframe.show(); 
      orderframe.setStatus("Getting orders of employee");

      this.first();
      // Loop through the contents of iterator to display them
      do {
        Vector row =new Vector();

        // Use super class methods to get the column values for each row
        row.addElement(this.ORDER_ID());
        row.addElement(this.FIRST_NAME()+" "+this.LAST_NAME());
        row.addElement(this.ORDER_MODE());
        row.addElement(this.ORDER_TOTAL());

        // insert the column values in a JTable row
        orderframe.tableModel.insertRow(row);
      } while (this.next());
        
      orderframe.setTitle("Order Details and Commission for "+empName); 

      // Display total Order value and commission on JLabels
      orderframe.labelTotal.setText("Order Total : $"+totalOrder);
      orderframe.labelCommission.setText("Commission Amount : $"+commission);
      orderframe.setStatus("Order details selected");
    } catch(Exception e){
      orderframe.setStatus(e.toString());
    }  
  }
}

/**
 * SQLJ Iterator is used for getting the query results returned by a SELECT 
 * statement.  This iterator can be extended to form a sub class. The sub class
 * will contain all the attributes of the super class plus additional
 * functionality can be included to it to enhance the query results and perform
 * operations on them.


 * This sample uses the OE and HR schemas built in with Oracle9i database.

 * Consider a product based company with sales representatives to sell the 
 * products. At the end of a particular period, the orders acquired by each
 * sales representatives are reviewed and based on the amount of the orders
 * collected, the commission for each one is decided.

 * The sample on start up displays all the sales representatives with their 
 * details.  The sample uses positional iterator to get this data from database.
 * Any sales representative can be selected to see the orders acquired by him 
 * and the commission to be given to him.
 * The order details of sales representative is obtained using a Named Iterator.
 * This iterator is further subclassed and a method is added to this subclass to
 * calculate the commission of the sales representative.

 * Method descrition in class
 *
 * getSalesRepresentatives() : gets the sales employee details from database
 * 
 * getOrders()               : gets the orders of an employee from database
 * 
 */
public class IterSubclassSample {
  // Declare object of frame class to show the sales representatives
  static IterSubclassEmpFrame gui;

  /**
   * Constructor definition
   */
  public IterSubclassSample()  {
    try {
      // Instantiate the frame class to display employee details
      gui = new IterSubclassEmpFrame();

      // display the frame
      gui.show();      
      gui.setStatus("Connecting to database...");

      // Get database connection using details like host, sid, username, password
      // from connect.properties file
      Oracle.connect( getClass(),"../../../../Connection.properties");

    } catch(Exception e){
      System.out.println("Error connecting to database "+e);
      System.exit(1);
    }
  }

 /**
  * This method defines entry point of the application
  */
  public static void main(String[] args) {
    try{

      // Instantiate the application
      IterSubclassSample iter = new IterSubclassSample();


      gui.setStatus("Getting sales representatives data");

      // call method to get the data of sales representative from database
      iter.getSalesRepresentatives();
    } catch(Exception e){
      gui.setStatus(e.toString());
    }
  }
  
  /**
   * This method gets the data for sales representatives from database using
   * SQLJ iterator
   */
  public void getSalesRepresentatives() throws Exception {

    // Create a Positional Iterator to hold employees info retrieved from
    // database
    #sql iterator EmpIter ( Integer, String ,String , String ,Float );

    // Instantiate the iterator
    EmpIter empList;

    // Declare variables to hold the details of employee
    Integer  empid=null;
    String empfname=null,emplname=null,deptname=null;
    Float commission=null;

    // Execute SQLJ statement to get details of all employees from database into
    // iterator object
    #sql   empList = { SELECT distinct(e.employee_id),e.first_name,e.last_name,
                       d.department_name ,e.commission_pct 
                       FROM employees e,departments d,orders o
                       WHERE e.department_id=d.department_id 
                       AND e.employee_id=o.sales_rep_id};
                    
    while ( true ) {
      // fetch details of employee from current iterator row into java 
      // host variables
      #sql { FETCH :empList INTO :empid, :empfname,:emplname,:deptname,
             :commission };

      // break the loop if end of iterator is reached 
      if (empList.endFetch()) break; 

      // Create a vector object to hold column values
      Vector row =new Vector();
      row.addElement(empid);
      row.addElement("  "+empfname +" "+emplname);
      row.addElement(deptname);
      row.addElement(commission);


      // insert the employee details in a JTable row
      gui.tableModel.insertRow(row);
    }    
    // close the iterator after fetching records
    empList.close();
    gui.setStatus("Select a sales representative");
  }

 /**
  * This method gets the orders of a particular sales representative from 
  * the database
  */
  public static void getOrders(int repID,String empName,float commPercent) {
    // Instantiate the SubOrderIter class to hold the order details
    SubOrderIter orderList;
    try {
      // Execute SQL query to get order details of selected employee
      #sql orderList ={ SELECT o.order_id,e.first_name,e.last_name,
                        o.order_mode,o.order_total FROM orders o,employees e
                        WHERE o.customer_id=e.employee_id 
                        AND sales_rep_id=:repID};    

      // call method to get order details from iterator object and calculate
      // the commission
      orderList.getOrdersAndCommission(empName,commPercent);

      //close the iterator after getting records    
      orderList.close();
    } catch(Exception e){
      gui.setStatus(e.toString());
    }
  }

 /**
  * This method is used to exit from the application
  */
  public static void exitApplication() {
    try {
      Oracle.close();
      System.exit(1);
    } catch(Exception e){
      gui.setStatus("Error closing application "+e.toString());
    }
  }
}