/*
 * @author  : Pushkala
 * @version : 1.0
 *
 * Development Environment : Oracle9i JDeveloper

 * Name of the File        : CMSXDBServletUtils.java
 *
 * Creation / Modification History
 *    Pushkala           25-Jan-2003        Created
 *
 */
package oracle.otnsamples.cmsxdb.dbmanager;


import java.util.Collection;
import java.util.ArrayList;
import java.util.Hashtable;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.CallableStatement;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;

import java.io.IOException;
import java.io.BufferedReader;

import oracle.sql.CLOB;


import oracle.otnsamples.cmsxdb.exception.CMSAccessException;

/**
 * This class acts as a helper for the Main Database Servlet
 * for performing the content management functionality.
 *
 */
public class CMSXDBServletUtils {

  /**

   * This method is called to get the folder or file details in the input
   * resource path. The details of each resource are put in a Hashtable object
   * and the Hashtable objects are put into a collection which is returned.
   *
   * @param    abspath      Resource path
   * @param    username     username
   * @param    type         Resource type 'FOLDER' or 'FILE'
   * @param    connection   database connection object
   *
   * @return   Collection   Collection of Hashtable objects with
   *                        file or folder details

   *
   * @exception  CMSAccessException  if any Exception occurs
   *
   */
  public static Collection getFolderContents( String abspath,
                                              String username,
                                              String type,
                                              Connection connection )
      throws CMSAccessException {

    // Query to get the list of all folders under the given path
    String  folderqry =

      " SELECT DISTINCT extractValue(link, '/LINK/Name/text()')  "+
      " FROM   XDB.path_view  "+
      " WHERE  UNDER_PATH(res, 1, ?) = 1  "+
      " AND    extractValue(res, '/Resource/@Container') = 'true' ";

    // Query to get the list of all files under the given path
    String  fileqry   =
      " SELECT DISTINCT extractValue(link, '/LINK/Name/text()')  "+
      " FROM   XDB.path_view  "+
      " WHERE  UNDER_PATH(res, 1, ?) = 1  "+
      " AND    extractValue(res, '/Resource/@Container') = 'false' ";

    // Query to get the details of the input folder

    String folderdtls =
      " SELECT DISTINCT TO_CHAR(extractvalue(res, '/Resource/Owner/text()')) "+
      "       ,SUBSTR(TO_CHAR(extractvalue(res, '/Resource/CreationDate/text()'), 'DD-MON-YYYY'), 1, 12) "+
      "       ,SUBSTR(TO_CHAR(extractvalue(res, '/Resource/ModificationDate/text()'), 'DD-MON-YYYY'), 1, 12) "+
      "       ,TO_CHAR(extractvalue(res, '/Resource/Language/text()')) "+
      " FROM   XDB.resource_view  "+
      " WHERE  any_path LIKE (? || '/' || ?) ";

    // Query to get the details of the input file
    String filedtls =
      " SELECT DISTINCT TO_CHAR(extractvalue(res, '/Resource/Owner/text()')) "+
      "       ,SUBSTR(TO_CHAR(extractvalue(res, '/Resource/CreationDate/text()'), 'DD-MON-YYYY'), 1, 12) "+
      "       ,SUBSTR(TO_CHAR(extractvalue(res, '/Resource/ModificationDate/text()'), 'DD-MON-YYYY'), 1, 12) "+
      "       ,TO_CHAR(extractvalue(res, '/Resource/Language/text()')) "+

      "       ,TO_CHAR(extractvalue(res, '/Resource/ContentType/text()')) "+
      "       ,TO_CHAR(NVL(extractvalue(res, '/Resource@VersionID'), '-1') ) "+
      "       ,TO_CHAR(NVL(DBMS_LOB.GETLENGTH(XDBUriType(any_path).getclob()), 0) ) "+
      " FROM   XDB.resource_view  "+
      " WHERE  any_path LIKE (? || '/' || ?) ";

    PreparedStatement pstmt   = null;
    ResultSet         rset    = null;

    PreparedStatement pstmtdtls = null;
    ResultSet         rsetdtls  = null;

    // Instantiate a collection to be returned
    Collection        retcoll = new ArrayList();


    try {

      if ("FOLDER".equals(type)) {

        pstmt = connection.prepareStatement(folderqry);
        pstmtdtls = connection.prepareStatement(folderdtls);

      } else if ("FILE".equals(type)) {

        pstmt = connection.prepareStatement(fileqry);
        pstmtdtls = connection.prepareStatement(filedtls);

      }

      pstmt.setString(1, abspath);
      pstmtdtls.setString(1, abspath);


      rset = pstmt.executeQuery();

      while (rset.next()) {

        // Populate a Hashtable with the values queried

        String name = rset.getString(1);

        Hashtable hash = new Hashtable();
        hash.put("DisplayName", name);

        try {
          pstmtdtls.setString(2, name);

          rsetdtls = pstmtdtls.executeQuery();


          if (rsetdtls.next()) {

            hash.put("Owner", rsetdtls.getString(1));
            hash.put("CreationDate", rsetdtls.getString(2));
            hash.put("ModificationDate", rsetdtls.getString(3));
            hash.put("Language", rsetdtls.getString(4));

            if ("FILE".equals(type)) {

              String contentType = rsetdtls.getString(5);

              if (name.toLowerCase().endsWith(".xml")) {

                // If it is an XML file, get the personalized view for the user
                contentType = getPersContentType(abspath+"/"+name,
                                                 username, connection);

              }


              hash.put("ContentType", contentType);

              hash.put("VersionId", rsetdtls.getString(6));

              String size = rsetdtls.getString(7);
              int intsize = (int)Math.ceil(Double.parseDouble(size) / 1000);

              hash.put("Size", Integer.toString(intsize));

            }

          }

          // Add the Hashtable object to the collection to be returned
          retcoll.add(hash);

        } catch(SQLException ex) {


          throw new CMSAccessException ( "SQL Exception in getFolderContents " +
                                         "for type : " + type + " " +
                                         "while getting details : " +
                                         ex.toString() );

        } finally {

          if (rsetdtls != null) rsetdtls.close(); // close the resultset

        }

      }

    } catch(SQLException ex) {

      throw new CMSAccessException (  "SQL Exception in getFolderContents " +
                                      "for type : " + type + " " +
                                      "while getting the list : " +
                                      ex.toString() );


    } finally {

      try {

        if ( rset != null )      rset.close();      // close the result set
        if ( pstmt != null )     pstmt.close();     // close the statement
        if ( pstmtdtls != null ) pstmtdtls.close(); // close the statement

      } catch(SQLException ex) {

        throw new CMSAccessException (  "SQL Exception in getFolderContents " +
                                        "while closing statements : " +
                                        ex.toString() );

      }

    }

    return retcoll;

  }



  /**
   * This method queries to check if personalization details
   * have been set by the User.
   *
   * @param    resource     Resource path
   * @param    username     username
   * @param    xslloc       XSL Location
   * @param    viewtype     View Type
   * @param    connection   database connection object
   *
   * @return   boolean      If record exists : TRUE
   *                        Else             : FALSE
   *
   * @exception  CMSAccessException  if any Exception occurs
   *
   */
  private static boolean checkView(String resource, String username,
                                   String xslloc, String viewtype,
                                   Connection connection)
      throws CMSAccessException {


    PreparedStatement pstmt   = null;
    ResultSet         rset    = null;
    String            query   = " SELECT viewtype, op.xslloc.getexternalurl() "+
                                " FROM   CMSADMIN.otncms_pers_view op         "+
                                " WHERE  UPPER(op.username) = UPPER(?)        "+
                                " AND    op.resourceloc.geturl() = ?          ";

    try {

      // Prepare the statement query
      pstmt = connection.prepareStatement(query);

      // Bind the input parameters
      pstmt.setString(1, username);
      pstmt.setString(2, resource);

      // Execute the query
      rset = pstmt.executeQuery();

      // Process the result set
      if (rset.next()) { // If records exist
        return true;

      }

    } catch(SQLException ex) { // Trap SQL Errors

      throw new CMSAccessException (  "SQLException in checkView " +
                                      ex.toString() );

    } finally {

      try {

        if (  rset != null )   rset.close(); // close the resultset
        if ( pstmt != null )  pstmt.close(); // close the statement

      } catch(SQLException ex) {

        throw new CMSAccessException (  "SQLException in checkView " +
                                        "while closing statements : "+
                                        ex.toString() );

      }

    }


    return false;

  }

  /**
   * This method queries to check if the XSL file passed is a resource.
   *
   * @param    xslloc       XSL Location
   * @param    connection   database connection object
   *
   * @return   boolean      If record exists : TRUE
   *                        Else             : FALSE
   *
   * @exception  CMSAccessException  if any Exception occurs
   *
   */
  private static boolean checkXSL(String xslloc, Connection connection )
      throws CMSAccessException {

    PreparedStatement pstmt      = null;
    ResultSet         rset       = null;
    String            query      = " SELECT any_path          "+
                                   " FROM   XDB.resource_view "+
                                   " WHERE  any_path = ?      ";


    try {

      // Prepare the statement query
      pstmt = connection.prepareStatement(query);

      // Bind the input parameters
      pstmt.setString(1, xslloc);

      // Execute the query
      rset = pstmt.executeQuery();

      // If records exist return true
      if (rset.next()) {
        return true;
      }

    } catch(SQLException ex) { // Trap SQL Errors

      throw new CMSAccessException (  "SQLException in checkXSL " +
                                      ex.toString() );

    } finally {

      try {

        if (  rset != null )    rset.close(); // close the resultset

        if ( pstmt != null )   pstmt.close(); // close the statement

      } catch(SQLException ex) {

        throw new CMSAccessException (  "SQLException in checkXSL " +
                                        "while closing statements : "+
                                        ex.toString() );

      }

    }

    return false;

  }

  /**
   * This method is called to set the personalization details for the User.
   *
   * @param    resource     Resource path
   * @param    username     username
   * @param    xslloc       XSL Location
   * @param    viewtype     View Type
   * @param    connection   database connection object
   *
   * @return   String       Record creation success message or
   *                        corresponding error message

   *
   * @exception  CMSAccessException  if any Exception occurs
   *
   */
  public static String insertView(String resource, String username,
                                  String xslloc, String viewtype,
                                  Connection connection)
      throws CMSAccessException {

    String   retMessage = "<EXC>Could not insert into CMSADMIN.OTNCMS_PERS_VIEW</EXC>";
    PreparedStatement pstmt   = null;
    String   query =
        " INSERT INTO CMSADMIN.otncms_pers_view          "+
        " ( USERNAME, RESOURCELOC, VIEWTYPE, XSLLOC )    "+
        " VALUES ( UPPER(?), SYS.XDBURITYPE(?), ?, SYS.XDBURITYPE(?) ) ";

    try {

      // Check if personalization details are already existing for the user
      boolean flag = checkView(resource, username, xslloc,
                               viewtype, connection);
      if (flag) {

        retMessage = "<EXC>Record already exists</EXC>";

      } else {

          // if record does not exist, insert the details


          boolean resxsl = true;
          if (!("".equals(xslloc))) {
            // If the xsl location is not null, check if it is a valid resource
            resxsl = checkXSL(xslloc, connection);
          }

          if (resxsl) {

            // Insert the personalization details
            pstmt = connection.prepareStatement(query);
            pstmt.setString(1, username);
            pstmt.setString(2, resource);
            pstmt.setString(3, viewtype);
            pstmt.setString(4, xslloc);

            if (pstmt.executeUpdate() == 1) {
              retMessage = "<MSG>Record created successfully</MSG>";
              connection.commit();
            }

          } else {

            retMessage = "<EXC>The XSL is not a resource</EXC>";

          }

      }


    } catch(SQLException ex) {

      throw new CMSAccessException (  "SQLException in insertView " +
                                      ex.toString() );

    } finally {

      try {

        if ( pstmt != null )   pstmt.close(); // close the statement

      } catch(SQLException ex) {

        throw new CMSAccessException (  "SQLException in insertView " +
                                        "while closing statements : "+
                                        ex.toString() );

      }

    }

    return retMessage;

  }


  /**
   * This method is called to update the personalization details for the User.
   *
   * @param    resource     Resource path

   * @param    username     username
   * @param    xslloc       XSL Location
   * @param    viewtype     View Type
   * @param    connection   database connection object
   *
   * @return   String       Record updation success message or
   *                        corresponding error message
   *
   * @exception  CMSAccessException  if any Exception occurs
   *
   */
  public static String updateView(String resource, String username,
                                  String xslloc,   String viewtype,
                                  Connection connection)
      throws CMSAccessException {

    String             retMessage = "<EXC>Could not update CMSADMIN.OTNCMS_PERS_VIEW</EXC>";
    PreparedStatement  pstmt      = null;
    String             query      = " UPDATE  CMSADMIN.otncms_pers_view op        "+
                                    " SET     op.viewtype = ?                     "+
                                    "       , op.xslloc   = SYS.XDBURITYPE(?)     "+
                                    " WHERE   UPPER(op.username)      = UPPER(?)  "+
                                    " AND     op.resourceloc.geturl() = ?         ";

    try {

      // Check if personalization details are already existing for the user
      boolean flag = checkView(resource, username, xslloc,
                               viewtype, connection);
      if (flag) {


        // if record exists, update the details

        boolean resxsl = true;
        if (!("".equals(xslloc))) {
          // If the xsl location is not null, check if it is a valid resource
          resxsl = checkXSL(xslloc, connection);
        }

        if (resxsl) {
          pstmt = connection.prepareStatement(query);
          pstmt.setString(1, viewtype);
          pstmt.setString(2, xslloc);
          pstmt.setString(3, username);
          pstmt.setString(4, resource);

          if (pstmt.executeUpdate() == 1) {
            retMessage = "<MSG>Record updated successfully</MSG>";
            connection.commit();
          }
        } else {
          retMessage = "<EXC>The XSL is not a resource</EXC>";
        }

      } else {

        retMessage = "<EXC>Record does not exist</EXC>";

      }

    } catch(SQLException ex) { // Trap SQL Errors

      throw new CMSAccessException (  "SQLException in updateView " +

                                      ex.toString() );

    } finally {

      try {

        if (pstmt != null) pstmt.close(); // close the statement

      } catch(SQLException ex) {

        throw new CMSAccessException (  "SQLException in updateView " +
                                        "while closing statements : "+
                                        ex.toString() );

      }

    }

    return retMessage;
  }


  /**
   * This method is used to convert the input CLOB value to a string
   *
   * @param    clob    CLOB value
   *
   * @return   String  String value corresponding to the CLOB value
   *
   * @exception  CMSAccessException  if any Exception occurs
   *
   */
  private static String getStringFromClob(CLOB clob)

      throws CMSAccessException {

    StringBuffer sb = new StringBuffer();
    BufferedReader clobData = null;

    try {

      // Use BufferedReader to read each line of the CLOB stream
      clobData = new BufferedReader(clob.getCharacterStream());
      String s;
      while( ( s = clobData.readLine( ) ) != null ) {
        // Append the values to a StringBuffer
        sb.append( s + "\n");
      }

    } catch (SQLException sqlex) {

      throw new CMSAccessException (  "SQLException in getStringFromClob " +
                                      sqlex.toString() );

    } catch (IOException ioex) {

      throw new CMSAccessException (  "IOException in getStringFromClob " +
                                      ioex.toString() );

    } finally {

      try {

        if ( clobData != null ) clobData.close();

      } catch (IOException ioex) {

        throw new CMSAccessException (  "IOException in getStringFromClob " +

                                        "while closing the CLOB data " +
                                        ioex.toString() );

      }

    }

    return sb.toString(); // return the string

  }


  /**
   * This method calls the PL/SQL package function to get the Contents of
   * the input version of the resource. The PL/SQL function returns a CLOB
   * value which is then converted to String value and returned
   *
   * @param    abspath       Resource path
   * @param    version       version number
   * @param    action        'EDITVERSION' or 'VIEWVERSION'
   * @param    connection    Database connection object
   *
   * @return   String        String value corresponding to the CLOB value
   *
   * @exception  CMSAccessException  if any Exception occurs
   *
   */
  public static String getPrevContents(String abspath, String version,
                                       String action, Connection connection)
      throws CMSAccessException {

    CallableStatement cstmt = null;
    String status  = "Failed to get contents of the previous version";
    String query   = " begin ? := CMSADMIN.otncms_xmlcontent.getprevcontents( "+
                                  " ?, ?, ?).getclobval(); end; ";


    try {

      // Prepare the statement
      cstmt = connection.prepareCall( query );

      // Register the OUT parameters
      cstmt.registerOutParameter(1, Types.CLOB);

      cstmt.setString(2, abspath); // Bind parameter
      cstmt.setString(3, version); // Bind parameter
      cstmt.setString(4, action); // Bind parameter

      cstmt.execute(); // Execute the query

      // Get the string value corresponding to the CLOB
      status = getStringFromClob((oracle.sql.CLOB)cstmt.getClob(1));

    } catch(SQLException ex) {

      throw new CMSAccessException (  "SQLException in getPrevContents " +
                                      ex.toString() );

    } finally {

      try {

        if (cstmt != null)  cstmt.close(); // close the statement

      } catch(SQLException ex) {

        throw new CMSAccessException (  "SQLException in getPrevContents " +
                                        "while closing statements : "+
                                        ex.toString() );

      }


    }

    return status;

  }

  /**
   * This method queries the personalization details of the User
   * and returns the ViewType set by the User if records exist.
   *
   * @param    resource      Resource path
   * @param    username      username
   * @param    connection    Database connection object
   *
   * @return   String        Content Type set by the User or default 'text/xml'
   *
   * @exception  CMSAccessException  if any Exception occurs
   *
   */
  private static String getPersContentType(String resource, String username,
                                           Connection connection)
      throws CMSAccessException {

    String contentType = "text/xml";

    PreparedStatement pstmt   = null;
    ResultSet         rset    = null;
    String  query =
        " SELECT viewtype                     "+
        " FROM   CMSADMIN.otncms_pers_view op "+
        " WHERE  op.username = ?              "+
        " AND    op.resourceloc.geturl() = ?  ";

    try {

      // Prepare the statement query

      pstmt = connection.prepareStatement(query);

      // Bind the input parameters
      pstmt.setString(1, username);
      pstmt.setString(2, resource);

      // Execute the query
      rset = pstmt.executeQuery();

      // If personalization records exist, get the content type set by the User
      if (rset.next()) {
        contentType = rset.getString(1);
      }

    } catch(SQLException ex) {

      throw new CMSAccessException (  "SQLException in getPersContentType " +
                                      ex.toString() );

    } finally {

      try {

        if (  rset != null )   rset.close(); // close the resultset
        if ( pstmt != null )  pstmt.close(); // close the statement

      } catch(SQLException ex) {

        throw new CMSAccessException (  "SQLException in getPersContentType " +
                                        "while closing statements : "+
                                        ex.toString() );

      }

    }

    return contentType;
  }


}