package devguide.advanced.refcursor;

import java.math.BigDecimal;

import java.sql.CallableStatement;
import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;
import java.sql.Timestamp;

import oracle.jbo.JboException;
import oracle.jbo.domain.Date;
import oracle.jbo.domain.Number;
import oracle.jbo.server.SQLBuilder;
import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;

import oracle.jdbc.OracleTypes;
// ---------------------------------------------------------------------
// ---    File generated by Oracle ADF Business Components Design Time.
// ---    Custom code may be added to this class.
// ---    Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class OrdersForCustomerImpl extends ViewObjectImpl {
    /**This is the default constructor (do not remove).
     */
    public OrdersForCustomerImpl() {
    }
    
    /**
     * Overridden framework method.
     * 
     * Executed when the framework needs to issue the database query for
     * the query collection based on this view object. One view object
     * can produce many related result sets, each potentially the result
     * of different bind variable values. If the rowset in query is involved
     * in a framework-coordinated master/detail viewlink, then the params array
     * will contain one or more framework-supplied bind parameters. If there
     * are any user-supplied bind parameter values, they will *PRECEED* the
     * framework-supplied bind variable values in the params array, and the
     * number of user parameters will be indicated by the value of the
     * numUserParams argument.
     */
    protected void executeQueryForCollection(Object qc,Object[] params,int numUserParams) { 
      storeNewResultSet(qc,retrieveRefCursor(qc,params));
      super.executeQueryForCollection(qc, params, numUserParams); 
    }

    /**
     * Overridden framework method.
     * 
     * Wipe out all traces of a built-in query for this VO
     */
    protected void create() {
      getViewDef().setQuery(null);
      getViewDef().setSelectClause(null);
      setQuery(null); 
    }

    /**
     * Overridden framework method.
     * 
     * The role of this method is to "fetch", populate, and return a single row
     * from the datasource by calling createNewRowForCollection() and populating
     * its attributes using populateAttributeForRow().
     */
    protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet rs) { 
      /*
       * We ignore the JDBC ResultSet passed by the framework (null anyway) and
       * use the resultset that we've stored in the query-collection-private
       * user data storage
       */
      rs = getResultSet(qc);
      
      /*
       * Create a new row to populate
       */
      ViewRowImpl r = createNewRowForCollection(qc);

      try {
        /*
         * Populate new row by attribute slot number for current row in Result Set
         */
        populateAttributeForRow(r,0, rs.getLong(1));
        populateAttributeForRow(r,1, rs.getString(2));
        populateAttributeForRow(r,2, rs.getString(3));
      }
      catch (SQLException s) {
       throw new JboException(s);
      }
      return r;
    }  

    /**
     * Overridden framework method.
     * 
     * Return true if the datasource has at least one more record to fetch.
     */
    protected boolean hasNextForCollection(Object qc) {
      ResultSet rs = getResultSet(qc);
      boolean nextOne = false;
      try {
        nextOne = rs.next();
        /*
         * When were at the end of the result set, mark the query collection
         * as "FetchComplete".
         */
        if (!nextOne) {
          setFetchCompleteForCollection(qc, true); 
          /*
           * Close the result set, we're done with it
           */
          rs.close();
        }
      }
      catch (SQLException s) {
       throw new JboException(s);
      }
      return nextOne;
    }

    /**
     * Overridden framework method.
     * 
     * The framework gives us a chance to clean up any resources related
     * to the datasource when a query collection is done being used.
     */
    protected void releaseUserDataForCollection(Object qc, Object rs) {
      /*
       * Ignore the ResultSet passed in since we've created our own.
       * Fetch the ResultSet from the User-Data context instead
       */
       ResultSet userDataRS = getResultSet(qc);
       if (userDataRS != null) {
        try {
          userDataRS.close();
        } 
        catch (SQLException s) {
          /* Ignore */
        }   
      }
      super.releaseUserDataForCollection(qc, rs);
    }

    /**
     * Overridden framework method
     * 
     * Return the number of rows that would be returned by executing
     * the query implied by the datasource. This gives the developer a
     * chance to perform a fast count of the rows that would be retrieved
     * if all rows were fetched from the database. In the default implementation
     * the framework will perform a SELECT COUNT(*) FROM (...) wrapper query
     * to let the database return the count. This count might only be an estimate
     * depending on how resource-intensive it would be to actually count the rows.
     */
    public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
      Long result = (Long)callStoredFunction(NUMBER,
                       "RefCursorExample.count_orders_for_customer(?)",
                       viewRowSet.getParameters(true));
      return result.longValue();                 
    }
    // ------------- PRIVATE METHODS ----------------
    

    /**
      * Return a JDBC ResultSet representing the REF CURSOR return
      * value from our stored package function.
      */
    private ResultSet retrieveRefCursor(Object qc, Object[] params) {
      ResultSet rs = (ResultSet)callStoredFunction(OracleTypes.CURSOR,
                       "RefCursorExample.get_orders_for_customer(?)",
                       new Object[]{getNamedBindParamValue("CustEmail",params)});
      return rs ;
    }
    private Object getNamedBindParamValue(String varName, Object[] params) {
      Object result = null;
      if (getBindingStyle() == SQLBuilder.BINDING_STYLE_ORACLE_NAME) {
        if (params != null) {
          for (Object param : params) {
            Object[] nameValue = (Object[])param;
            String name = (String)nameValue[0];
            if (name.equals(varName)) {
              return (String)nameValue[1];
            }
          }
        }
      }
      throw new JboException("No bind variable named '"+varName+"'");
    }
    /**
     * Store a new result set in the query-collection-private user-data context
     */
    private void storeNewResultSet(Object qc, ResultSet rs) {
      ResultSet existingRs = getResultSet(qc);
      // If this query collection is getting reused, close out any previous rowset
      if (existingRs != null) {
        try {existingRs.close();} catch (SQLException s) {}   
      }
      setUserDataForCollection(qc,rs);
      hasNextForCollection(qc); // Prime the pump with the first row.
    }

    /**
     * Retrieve the result set wrapper from the query-collection user-data
     */
    private ResultSet getResultSet(Object qc) {
      return (ResultSet)getUserDataForCollection(qc);
    }

    /**
     * Return either null or a new oracle.jbo.domain.Date
     */
    private static Date nullOrNewDate(Timestamp t) {
      return t != null ? new Date(t) : null;
    }

    /**
     * Return either null or a new oracle.jbo.domain.Number
     */
    private static Number nullOrNewNumber(BigDecimal b) {
      try {
        return b != null ? new Number(b) : null;
      }
      catch (SQLException s) { }
      return null;
    }
    //----------------[ Begin Helper Code ]------------------------------
    public static int NUMBER = Types.NUMERIC;
    public static int DATE = Types.DATE;
    public static int VARCHAR2 = Types.VARCHAR;

    /**
     * Simplifies calling a stored function with bind variables
     * 
     * You can use the NUMBER, DATE, and VARCHAR2 constants in this
     * class to indicate the function return type for these three common types,
     * otherwise use one of the JDBC types in the java.sql.Types class.
     * 
     * NOTE: If you want to invoke a stored procedure without any bind variables
     * ====  then you can just use the basic getDBTransaction().executeCommand()
     * 
     * @param sqlReturnType JDBC datatype constant of function return value
     * @param stmt stored function statement
     * @param bindVars Object array of parameters
     * @return function return value as an Object
     */
    protected Object callStoredFunction(int sqlReturnType, String stmt,
      Object[] bindVars) {
      CallableStatement st = null;
      try {
        st = getDBTransaction().createCallableStatement("begin ? := " + stmt +
            "; end;", 0);
        st.registerOutParameter(1, sqlReturnType);
        if (bindVars != null) {
          for (int z = 0; z < bindVars.length; z++) {
            st.setObject(z + 2, bindVars[z]);
          }
        }
        st.executeUpdate();
        return st.getObject(1);
      }
      catch (SQLException e) {
        throw new JboException(e);
      }
    }

    /**getEstimatedRowCount - overridden for custom java data source support.
     */
    public long getEstimatedRowCount() {
      long value = super.getEstimatedRowCount();
      return value;
    }
    
    /**Gets the bind variable value for CustEmail
     */
    public String getCustEmail() {
      return (String)getNamedWhereClauseParam("CustEmail");
    }

    /**Sets <code>value</code> for bind variable Email
     */
    public void setCustEmail(String value) {
      setNamedWhereClauseParam("CustEmail", value);
    }
}
