/*
 *                 IFS Research & Development
 *
 *  This program is protected by copyright law and by international
 *  conventions. All licensing, renting, lending or copying (including
 *  for private use), and all other use of the program, which is not
 *  expressively permitted by IFS Research & Development (IFS), is a
 *  violation of the rights of IFS. Such violations will be reported to the
 *  appropriate authorities.
 *
 *  VIOLATIONS OF ANY COPYRIGHT IS PUNISHABLE BY LAW AND CAN LEAD
 *  TO UP TO TWO YEARS OF IMPRISONMENT AND LIABILITY TO PAY DAMAGES.
 * ----------------------------------------------------------------------------
 * File        : PlsqlAccess.java
 * Description :
 * Notes       :
 * ----------------------------------------------------------------------------
 * Modified
 *   HEDJSE 2003-Aug-01 - Created.
 * ----------------------------------------------------------------------------
 *
 */

import ifs.fnd.ap.*;

/**
 * <P>
 * Example class for accessing PL/SQL from the Java Access Provider.
 * As you can see, there is no error handling in this class. A real application
 * must always handle the errors. This is just an easy way to avoid cluttering
 * the example code (since error handling isn't the topic here).
 * </P><P>
 * As you can see in the code, <B>all</B> package, table and view names must be prefixed
 * with '&AO.'. This is in the PL/SQL Gateway replaced with the application owner.
 * </P>
 */
public class PlsqlAccess {
   
   private Server srv;
   
   /**
    * Creates a new instance of this class.
    */
   public PlsqlAccess(String connectionString, String userid, String password) {
      srv = new Server();
      
      // Set the server object's connection string (where to connect to).
      srv.setConnectionString(connectionString);
      
      // Set the server object's credentials. Must be set to something real.
      srv.setCredentials(userid, password);
      
      // Set a locale (a locale (=language) must ALWAYS be used when accessing PL/SQL).
      // In this case: en
      srv.setLocale("en");
   }
   
   /**
    * Gets a user's description by calling </code>FND_USER_API.GET_DESCRIPTION</code>.
    * @param   identity the user's identity.
    * @return  the user's description.
    */
   public String getDescription(String identity) throws APException {
      // Create the PL/SQL command block. A method call inside a BEGIN END block.
      PlsqlCommand cmd = new PlsqlCommand(srv, "BEGIN :DESC := &AO.FND_USER_API.GET_DESCRIPTION(:IDENTITY); END;");     
      // Set bind variables values and direction (a Record is used for bind variables)
      Record bindVars = cmd.getBindVariables();
      bindVars.add("IDENTITY", identity).setBindVariableDirection(BindVariableDirection.IN);
      // The OUT-parameter don't need an initial value.
      bindVars.add("DESC").setBindVariableDirection(BindVariableDirection.OUT);      
      
      // Execute call
      cmd.execute();

      // Return the OUT value.
      return bindVars.find("DESC").getString();      

   }
   
   /**
    * Gets info on a user by doing a SQL query with a bind variable.
    * @param   identity the identity of the user to get the info for.
    * @return  a Record with the user info. Returns null if no info is found.
    */
   public Record getUser(String identity) throws APException {
      // The SQL query to use.
      String sql = "SELECT IDENTITY,DESCRIPTION,ORACLE_USER,WEB_USER,ACTIVE FROM &AO.FND_USER WHERE IDENTITY=:IDENTITY";     
      // Use a PlsqlSelectCommand object for queries.
      PlsqlSelectCommand selCmd = new PlsqlSelectCommand(srv, sql);
            
      // Set bind variable value and direction.
      Record bindVars = selCmd.getBindVariables();
      bindVars.add("IDENTITY", identity).setBindVariableDirection(BindVariableDirection.IN);

      // Excute the query and get the result.
      RecordCollection result = selCmd.executeQuery();

      // See if there is a result and if any rows were returned.
      if(result != null && result.size() > 0)
         return result.get(0);   // Return the first row (and in this case the only one).

      return null;
   }
   
   /**
    * Prints out a list of all users by doing a simple SQL query.
    */
   public void listUsers() throws APException {
      // Queries are performed with a PlsqlSelectCommand object.
      PlsqlSelectCommand selCmd = new PlsqlSelectCommand(srv, "SELECT IDENTITY, DESCRIPTION FROM &AO.FND_USER");
      
      // Excute the query and get the result.
      RecordCollection result = selCmd.executeQuery();
      
      System.out.println("All users:");
      
      // Check if there are any rows returned.
      if(result != null && result.size() > 0) {
         // Iterate through the result set and print out a list of the users.
         RecordIterator itr = result.recordIterator();
         Record next;
         while(itr.hasNext()) {
            next = itr.nextRecord();
            System.out.println("  " + next.findValue("IDENTITY") + "\t" + next.findValue("DESCRIPTION"));
         }
      }
      else   // Print out a nice message if there is no result.
         System.out.println("No rows selected");
   }
   
   /**
    * Combines all the other operations and does them in one call to the 
    * PL/SQL Gateway using a PlsqlCommandCollection.
    */
   public void allInOne(String identity) throws APException {
      // A PlsqlCommandCollection is used for executing multiple commands in one call.
      PlsqlCommandCollection cmds = new PlsqlCommandCollection(srv);
    
      // Setup the commands to execute and add them to the collection.
      // Don't associate them with a Server object, since that is done on the collection instead.
      
      PlsqlCommand getDescCmd = new PlsqlCommand("BEGIN :DESC := &AO.FND_USER_API.GET_DESCRIPTION(:IDENTITY); END;");           
      getDescCmd.getBindVariables().add("IDENTITY", identity).setBindVariableDirection(BindVariableDirection.IN);
      getDescCmd.getBindVariables().add("DESC").setBindVariableDirection(BindVariableDirection.OUT);      
      cmds.add(getDescCmd);
      
      PlsqlSelectCommand selCmd = new PlsqlSelectCommand("SELECT IDENTITY,DESCRIPTION,ORACLE_USER,WEB_USER,ACTIVE FROM &AO.FND_USER WHERE IDENTITY=:IDENTITY");
      selCmd.getBindVariables().add("IDENTITY", identity).setBindVariableDirection(BindVariableDirection.IN);      
      cmds.add(selCmd);
      
      PlsqlSelectCommand listUsersCmd = new PlsqlSelectCommand("SELECT IDENTITY, DESCRIPTION FROM &AO.FND_USER");
      cmds.add(listUsersCmd);
      
      // Execute the commands
      cmds.execute();
      
      // Handle the results
      
      System.out.println("Description for user '" + identity + "': " + getDescCmd.getBindVariables().find("DESC").getString());
      
      System.out.println("Info on user '" + identity + "': ");
      if(selCmd.getResult() != null && selCmd.getResult().size() > 0)
         System.out.println(selCmd.getResult().get(0));
      else
         System.out.println("No user '" + identity + "' found.");
      
      System.out.println();
      System.out.println("All users:");
      if(listUsersCmd.getResult() != null && listUsersCmd.getResult().size() > 0) {
         RecordIterator itr = listUsersCmd.getResult().recordIterator();
         Record next;
         while(itr.hasNext()) {
            next = itr.nextRecord();
            System.out.println("  " + next.findValue("IDENTITY") + "\t" + next.findValue("DESCRIPTION"));
         }
      }
      else
         System.out.println("No rows selected");
   }
   
   /**
    * Main method.
    * @param args the command-line arguments to this application.
    */
   public static void main(String[] args) throws APException {
      // Check parameters.
      if(args.length != 3) {
         System.err.println("Usage: PlsqlAccess <connction_string> <domain\\username> <password>");
         System.exit(1);
      }
      
      // Create an instance of this object.
      PlsqlAccess pa = new PlsqlAccess(args[0], args[1], args[2]);

      // Call the methods.
      System.out.println("Description for user 'IFSAPP': " + pa.getDescription("IFSAPP"));
      System.out.println("Info on user 'IFSAPP': ");
      System.out.println(pa.getUser("IFSAPP"));
      pa.listUsers();

      System.out.println();
      System.out.println("All in one call to PL/SQL Gateway");
      pa.allInOne("IFSAPP");
   }
}