package gov.maine.node;

import gov.epa.cdx.axis.v10.NetworkNodePortType;
import java.rmi.RemoteException;
import gov.epa.cdx.axis.v10.vo.holders.ArrayofDocHolder;
import gov.epa.cdx.axis.v10.vo.*;
import gov.epa.cdx.axis.security.enum.AuthMethod;
import gov.epa.cdx.axis.fault.FaultErrorCode;
import gov.epa.cdx.axis.fault.FaultHandler;
import javax.servlet.http.*;
import java.math.BigInteger;
import gov.epa.cdx.commons.exception.*;
import gov.epa.cdx.commons.log.LogInterface;
import gov.epa.cdx.commons.log.LogFactory;
import gov.epa.state.sample.security.SecurityMgr;
import gov.epa.state.sample.axis.converter.ResultConverter;
import org.apache.axis.transport.http.HTTPConstants;
import org.apache.axis.AxisFault;
import gov.epa.cdx.commons.util.GUID;

//import statements for Maine node code
import java.io.*;
import java.net.*;
import java.util.*;
import java.lang.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.query.OracleXMLQuery;
import oracle.xml.parser.v2.*;
import oracle.xml.parser.schema.*;
import org.w3c.dom.*;
import gov.maine.node.util.*;
import oracle.jdbc.OracleTypes.*;
import oracle.sql.*;
import oracle.jdbc.driver.OracleCallableStatement;



/**
 * This class reprsents the main entry point into the node. 
 * 
 * 
 * @author David Dundua
 * @version 1.0
 */
public class Node implements NetworkNodePortType
{
  private static LogInterface log = LogFactory.getLog(Node.class.getName());

  // *********************************************************
  // Global variables
  // *********************************************************
     static boolean inservice = true;
     static String connectString = "jdbc:oracle:thin:@<host_name>:<port>:<oracle_sid>";
     static String db = "<oracle_sid>";
     static Integer rowIdDefault = new Integer(0);
     static Integer maxRowsDefault =new Integer(500);
     private String faultString = null; // to hold the Exception message to put in AxisFault
  // *********************************************************


  
  public Node()
  {
  }

  public String authenticate(String userId, String credential,
                             String authenticationMethod) throws RemoteException
  {
     String token = null;
     String servTrakId = null;
     Connection dbConnection = null;
     OracleCallableStatement cstmt=null;
     String serviceId = "2";
     WebServicesTracking serviceTracking = null;   
     try
     {
        dbConnection = createDBConnection(db);
        dbConnection.setAutoCommit(false);
        
        // log serviceId & user information in service_tracking_log table.
        serviceTracking = new WebServicesTracking(userId, serviceId, "REQUESTED", dbConnection);
        serviceTracking.writeToDB(dbConnection);

        // The token comes from a call to NAAS, the centralized authentication service.
        token = SecurityMgr.centralAuth(userId, credential,
                                  AuthMethod.password, this.getClientHost());

        // store this user in the user_profile table
        cstmt = (OracleCallableStatement) 
                dbConnection.prepareCall("{call STORED_PROCEDURES.writeUserProfile(?,?)}");
        cstmt.setString(1,userId);
        cstmt.setString(2,token);
        cstmt.execute();
        cstmt.close();
        dbConnection.commit();
        
        // update the service_tracking_log table.
        serviceTracking.setTrackingStatus("SUCCEED");
        serviceTracking.writeToDB(dbConnection);
        dbConnection.close();
     }
     catch (Exception e)
     {

        try 
        {
           dbConnection.rollback();
           serviceTracking.setTrackingStatus("FAILED");
           serviceTracking.writeToDB(dbConnection);
           if (cstmt != null)
           {
              cstmt.close();
           }
           if (dbConnection != null)
           {
              dbConnection.close();
           }
        }
        catch (Exception e1)
        {
        }
        faultString = e.getMessage();
        AxisFault fault = FaultHandler.getFault("Client", faultString, FaultErrorCode.UNKNOWN_USER);
        throw fault;
     }
     return token;
  }


  public String submit(String securityToken, String transactionId, String dataflow, NodeDocument[] documents) throws RemoteException
  {
     String r_transactionId = null;;
     Connection dbConnection = null;
     String sql = null;
     String serviceTrackingId = null;
     String userId = null;
     String documentName = null;
     String documentType = null;
     String documentContent = null;
     Clob docContent = null;
     String enc="UTF-8";
     String serviceId = "14";
     PreparedStatement ps = null;
     
   //  OracleCallableStatement ocs = null;
     ResultSet         rs = null;
     WebServicesTracking servicesTracking = null;
     Transaction serverTransaction = null;
     String error = null;     
     try
     {
       if (this.validateToken(securityToken, this.getClientHost()))
       {
         dbConnection = createDBConnection(db);
         dbConnection.setAutoCommit(false);

         //pull out the user Id matched with the token.
         userId = getUser(securityToken);
         
         // log serviceId & user information in service_tracking_log table.
         servicesTracking = new WebServicesTracking(userId, serviceId, "REQUESTED", dbConnection);
         servicesTracking.writeToDB(dbConnection);
         serviceTrackingId = servicesTracking.getTrackingId();
         
         // if the user not authorized, Exception thrown in isAuthorize method.
         if (isAuthorize(userId,serviceId))
         {
           // in the event that this node called the <solicit> method on another node
           // this call to submit, should have a transactionId.
           if (transactionId != null)
           {
              sql = "SELECT client_transaction_id from outgoing_solicit where client_transaction_id = ?";
              ps = dbConnection.prepareStatement(sql);
              ps.setString(1,transactionId);
              rs = ps.executeQuery();
              if (!rs.next())
              {
                throw new Exception ("Transaction ID does not exist"); 
              }
              // close up this statement.
              rs.close();
              ps.close();
           } 
           
           // update the client transaction Id table for the solicited operation  
           GUID idGen = new GUID();
           serverTransaction = new Transaction();
           serverTransaction.setServerTransactionId(idGen.getGUID());
           serverTransaction.setServiceTrackingId(serviceTrackingId);
           serverTransaction.setUserId(userId);
           serverTransaction.setStatus("PROCESSING");
           r_transactionId  = serverTransaction.getServerTransactionId();

           // write to transaction table
           serverTransaction.writeToDB(dbConnection);


           //get the information from docs []
           for (int i=0 ; i < documents.length ; i ++)
           {
             documentName = documents[i].getName();
             documentType = documents[i].getType();

             // first put the document content into a String object.
             documentContent = new String(documents[i].obtainContentBytes(), enc);
             // next instantiate a StringReader to read the bytes from the string.
             StringReader sr = new StringReader(documentContent);
             // build the sql string.
             sql = "BEGIN  "+
                   "INSERT INTO idocument values (?,?,?,?,empty_clob())" +
                   "return DOCUMENT_CONTENT into ? ;  " +
                   "END;";
             // build the OracleCallableStatement.
             OracleCallableStatement ocs = (OracleCallableStatement) dbConnection.prepareCall(sql);
             ocs.setString(1, transactionId);
             ocs.setString(2, r_transactionId);
             ocs.setString(3, documentType);
             ocs.setString(4, documentName);
             ocs.registerOutParameter(5, OracleTypes.CLOB);

             ocs.execute();

             // get a handle to the Clob object returned from the call to the database.
             CLOB clob = ocs.getCLOB(5);

             // instantiate a writer to send the bytes to the Clob.
             Writer wr = clob.getCharacterOutputStream();
             int size = clob.getBufferSize();
             char buffer[] = new char[size];
             int wcount = 0;
             int length = 0;

             // write the bytes from the documentContent String into the Clob.
             while ( (length = sr.read(buffer, 0, size)) != -1)
             {
               wcount += length;
               wr.write(buffer, 0, length);
             }

             // close all streams and commit the document.
             sr.close();
             wr.close();
             ocs.close();
             dbConnection.commit();

           } //for loop

           dbConnection.commit();

           // write to transaction table
           serverTransaction.setStatus("RECEIVED");
           serverTransaction.updateToDB(dbConnection);

           // log updated info in service_tracking_log table.
           servicesTracking.setTrackingStatus("SUCCEED");
           servicesTracking.writeToDB(dbConnection);

           dbConnection.close();

           return r_transactionId;
           } // user Authorized.
           else
           {
             throw new Exception("User not Authorized for this Service.");
           }
              
       } // valid security token.

     }  // end of try block.
     catch (Exception e)
     {
        try 
        {
          dbConnection.rollback();
          servicesTracking.setTrackingStatus(e.getMessage());
          servicesTracking.writeToDB(dbConnection);
          serverTransaction.setStatus("FAILED");
          serverTransaction.updateToDB(dbConnection);
          if (ps !=null )
          {
            ps.close();
          }
          if (dbConnection!=null)
          {
            dbConnection.close();
          }
        }
        catch (Exception e1)
        {
        }
        faultString = e.getClass() + ": " + e.getMessage()+": "+error;
        AxisFault fault = FaultHandler.getFault("Server", faultString, FaultErrorCode.INTERNAL_ERROR);
        throw fault;
     }
          return null;
  }


  public String getStatus(String securityToken, String transactionId) throws RemoteException
  {
    String res_status = null;;
    Connection dbConnection = null;
    String sql = null;
    String servTrakId = null;
    PreparedStatement ps=null;
    ResultSet rs = null;
    String userId = null;
    WebServicesTracking servicesTracking = null;
    String serviceId = "16";
    try
    {
      if  (this.validateToken(securityToken, this.getClientHost()))
      {
        dbConnection = createDBConnection(db);
        dbConnection.setAutoCommit(false);

        //pull out the userId using the token.
        userId = getUser(securityToken);
        servicesTracking = new WebServicesTracking(userId,serviceId,"REQUESTED",dbConnection);
        servicesTracking.writeToDB(dbConnection);
        
        // if the user not authorized, Exception thrown in isAuthorize method.
        if (isAuthorize(userId, serviceId))
        {
          sql = "SELECT status from TRANSACTION where server_transaction_id = ? and USER_ID=?";
          ps = dbConnection.prepareStatement(sql);
          ps.setString(1,transactionId);
          ps.setString(2,userId);
          rs = ps.executeQuery();
          if (!rs.next())
          {
            throw new Exception("Invalid Transaction Id");
          }           
          else
          {
            res_status = rs.getString("STATUS");
            servicesTracking.setTrackingStatus("SUCCEED");
            servicesTracking.writeToDB(dbConnection);
            rs.close();
            ps.close();
            dbConnection.commit();
            dbConnection.close();
            return res_status;
          }
        }
        else
        {
          throw new Exception("User not Authorized for this Service.");
        }
      }
      return null;
    }
    catch (Exception e)
    {
       //setServiceStatus(servTrakId,e.getMessage());
       try 
       {
         servicesTracking.setTrackingStatus(e.getMessage());
         servicesTracking.writeToDB(dbConnection);
         if (rs != null)
         {
           rs.close();
         }
         if (ps != null)
         {
           ps.close();
         }
         if (dbConnection != null)
         {
           dbConnection.close();
         }
       }
       catch (Exception e1)
       {
       }
       faultString = e.getMessage();
       AxisFault fault = FaultHandler.getFault("Server", faultString, FaultErrorCode.INTERNAL_ERROR);
       throw fault;
    }
  }


  public String notify(String securityToken, String nodeAddress, String dataflow, NodeDocument[] documents) throws RemoteException
  {
     Connection dbConnection= null;
     String returnValue = null;
     String serviceId = new String("11");
     String status = new String("Recieved");
     String transactionId = null;
     String serviceTrackingId = null;
     String documentName = null;
     String documentType = null;
     String documentContent = null;
     Clob docContent = null;
     String userId = null;
     String sql = null;
     String enc ="UTF-8";
     PreparedStatement ps=null;
     OracleCallableStatement ocs = null;
     ResultSet rs = null;
     WebServicesTracking servicesTracking = null;
     Transaction serverTransaction = null;
     // check users token, if valid continue.
     try 
     {  
        if (this.validateToken(securityToken, this.getClientHost()))
        {
          //log in Service_Tracking table that user has called the Notify method.
          dbConnection = createDBConnection(db);
          dbConnection.setAutoCommit(false);

          //pull out the userId using the token.
          userId = getUser(securityToken);
          servicesTracking = new WebServicesTracking(userId,serviceId,"REQUESTED",dbConnection);
          servicesTracking.writeToDB(dbConnection);
         
          // if the user not authorized, Exception thrown in isAuthorize method.
          if (isAuthorize(userId, serviceId))
          {
             //Store information from the method call in the Notify_Log table.
             //loop through the array of NodeDocuments until all are stored in DB.

             serviceTrackingId = servicesTracking.getTrackingId();
             serverTransaction = new Transaction();
             GUID idGen = new GUID();
             serverTransaction.setServerTransactionId(idGen.getGUID());
             serverTransaction.setServiceTrackingId(serviceTrackingId);
             serverTransaction.setUserId(userId);
             serverTransaction.setStatus("PROCESSING");
             serverTransaction.writeToDB(dbConnection);
             transactionId = serverTransaction.getServerTransactionId();
             
             // If Notify.Status change the status of the transactionId passed in.
             if (dataflow.equalsIgnoreCase("http://www.neien.org/cdx/status"))
             {
                // Check if the tx. id. exist in the system.
                sql = "SELECT client_transaction_id from outgoing_solicit where client_transaction_id = ?";
                ps = dbConnection.prepareStatement(sql);
                ps.setString(1,documentName);
                rs = ps.executeQuery();
                if (!rs.next())
                {
                   throw new Exception ("Transaction id invalid"); 
                }
                rs.close();
                //dbConnection.close();
             }

             /* write to incoming_notification table */
             sql = "INSERT INTO  incoming_notification values (?,?,?,?,sysdate)";
             ps = dbConnection.prepareStatement(sql);
             ps.setString(1,transactionId);
             ps.setString(2,userId);
             ps.setString(3,nodeAddress);
             ps.setString(4,dataflow);
             ps.execute();
             ps.close();
             /* write to document table */
             for (int i=0 ; (i < documents.length) && (documents[i] !=null)
               && (documents[i].getName().length()>0) ; i++)
             {
                //get the information from docs []
                documentName = documents[i].getName();
                documentType = documents[i].getType();
                // the documentContent element is not required, if missing indicate so.
                try
                {
                  // if it exists put the document content into a String object.
                  documentContent = new String(documents[i].obtainContentBytes(), enc);
                }
                catch (Exception e)
                {
                  documentContent = new String("No content in this Document.");
                }

                // next instantiate a StringReader to read the bytes from the string.
                StringReader sr = new StringReader(documentContent);
                // build the sql string.
                sql = "BEGIN  "+
                   "INSERT INTO idocument values (null,?,?,?,empty_clob())" +
                   "return DOCUMENT_CONTENT into ? ;  " +
                   "END;";
                // build the OracleCallableStatement.
                ocs = (OracleCallableStatement) dbConnection.prepareCall(sql);
                ocs.setString(1, transactionId);
                ocs.setString(2, documentType);
                ocs.setString(3, documentName);
                ocs.registerOutParameter(4, OracleTypes.CLOB);
                ocs.execute();

                // get a handle to the Clob object returned from the call to the database.
                CLOB clob = ocs.getCLOB(4);

                // instantiate a writer to send the bytes to the Clob.
                Writer wr = clob.getCharacterOutputStream();
                int size = clob.getBufferSize();
                char buffer[] = new char[size];
                int wcount = 0;
                int length = 0;
                // write the bytes from the documentContent String into the Clob.
                while ( (length = sr.read(buffer, 0, size)) != -1)
                {
                  wcount += length;
                  wr.write(buffer, 0, length);
                }
                // close all streams and commit the document.
                sr.close();
                wr.close();
                ocs.close();
             }   //end of NodeDocument loop.
               
             dbConnection.commit();
             /* update transaction status table */
             serverTransaction.setStatus("RECEIVED");
             serverTransaction.updateToDB(dbConnection);
             servicesTracking.setTrackingStatus("SUCCEED");
             servicesTracking.writeToDB(dbConnection);
             dbConnection.close();
             return transactionId;
          }// isAuthorized block
          else
          {
            throw new Exception("User not Authorized for this Service.");
          }

        }// valid token block
        // if user's token is invalid an exception should be thrown,
        // if the exception is not thown,  don't do anything (for now).
        return null;
          
     } // end of try block
     catch (Exception e)
     {   
        try 
        {
           dbConnection.rollback();
           servicesTracking.setTrackingStatus(e.getMessage());
           servicesTracking.writeToDB(dbConnection);
           serverTransaction.setStatus("FAILED");
           serverTransaction.updateToDB(dbConnection);
         
           if (rs!=null)
           {
              rs.close();
           }
           if (ps != null)
           {
              ps.close();
           }
           if (dbConnection != null)
           {
              dbConnection.close();
           }
        }
        catch (Exception e1 )
        {
        }
        faultString = e.getMessage();
        AxisFault fault = FaultHandler.getFault("Server", faultString, FaultErrorCode.INTERNAL_ERROR);
        throw fault;
     }
  }

    
  public void download(String securityToken, String transactionId, String dataflow,
                        ArrayofDocHolder documents) throws RemoteException
  {
     String result_str = null;
     String serviceTrackingId = null;
     String userId = null;
     ResultSet rs = null;
     ResultSet downloadDocs = null;
     Connection dbConnection=null;
     PreparedStatement ps = null;
     String sql = null;
     StringBuffer sqlBuffer = new StringBuffer(578);
     String res_status = null;
     WebServicesTracking servicesTracking = null;
     String serviceId = "17";
     //this is a reference to recieved document request
     NodeDocument [] requestedDocumentsArray = documents.value;
     //here we can respond to the request with documents in this case we will simply iterate over the requested documents
     //create a new array of documents and repond
     NodeDocument [] responseDocumentsArray = new NodeDocument[requestedDocumentsArray.length];
     try
     {
        if  (this.validateToken(securityToken, this.getClientHost()))
        {
           dbConnection = createDBConnection(db);
           dbConnection.setAutoCommit(false);
            
           //pull out the userId using the token.
           userId = getUser(securityToken);
           servicesTracking = new WebServicesTracking(userId,serviceId,"REQUESTED",dbConnection);
           servicesTracking.writeToDB(dbConnection);

           // if the user not authorized, Exception thrown in isAuthorize method.
           if (isAuthorize(userId, serviceId))
           {
              if (transactionId != null) 
              {
                 sql = "SELECT incoming_download_id from INCOMING_DOWNLOAD where server_transaction_id = ? and USER_ID=? and DATAFLOW = ?";
                 ps = dbConnection.prepareStatement(sql);
                 ps.setString(1,transactionId);
                 ps.setString(2,userId);
                 ps.setString(3,dataflow);
                 rs = ps.executeQuery();
                 if (!rs.next())
                 {
                    throw new Exception("No Files for this transaction id/file id");
                 }
                 else
                 {
                    int downloadId = rs.getInt("INCOMING_DOWNLOAD_ID");
                    sql = "SELECT document_type, document_name, document_content from WDOCUMENT where incoming_download_id = ?";
                    ps = dbConnection.prepareStatement(sql);
                    ps.setInt(1,downloadId);
                    downloadDocs = ps.executeQuery();
                    int counter = 0;
                    while (downloadDocs.next())
                    {
                       NodeDocument xdoc = new NodeDocument();
                       xdoc.setName(downloadDocs.getString("DOCUMENT_NAME"));
                       xdoc.setType(downloadDocs.getString("DOCUMENT_TYPE"));
                       byte [] content = downloadDocs.getString("DOCUMENT_CONTENT").getBytes();
                       xdoc.putContent(content,
                       NodeDocumentContentConverter.CONTENT_TYPE_ATTACHMENT);
                       responseDocumentsArray[counter] = xdoc;
                       counter++;
                    }
                    rs.close();
                    downloadDocs.close();
                    ps.close();
                    dbConnection.commit();
                    dbConnection.close();
                    //last but definetly not least insert this into the document holder
                    //this line of code handles the return of the node documents to the
                    // requestor by using functionality provided by CSC.
                    documents.value = responseDocumentsArray;
                 }
              }
              else  // no transaction Id for this call.
              {
                 // exchange document , no transaction id //
                 for (int i = 0 ; i < requestedDocumentsArray.length ; i++ )
                 {
                    String documentName = requestedDocumentsArray[i].getName();                              
                    String documentType = requestedDocumentsArray[i].getType();
                    sqlBuffer.append("Select document_content ");
                    sqlBuffer.append("from WDOCUMENT, INCOMING_DOWNLOAD ");
                    sqlBuffer.append("where INCOMING_DOWNLOAD.incoming_download_id = WDOCUMENT.incoming_download_id ");
                    sqlBuffer.append("and user_id = ? and dataflow = ? ");
                    sqlBuffer.append("and document_name = ? and document_type = ?");
                    ps = dbConnection.prepareStatement(sqlBuffer.toString());
                         
                    ps.setString(1,userId);
                    ps.setString(2,dataflow);
                    ps.setString(3,documentName);
                    ps.setString(4,documentType);
                    rs = ps.executeQuery();
                    NodeDocument xdoc = new NodeDocument();
                    xdoc.setName(requestedDocumentsArray[i].getName());
                    xdoc.setType(requestedDocumentsArray[i].getType());
                       
                    if (rs.next())
                    {
                       byte [] content = rs.getString("DOCUMENT_CONTENT").getBytes();
                       xdoc.putContent(content,
                           NodeDocumentContentConverter.CONTENT_TYPE_ATTACHMENT);
                    }
                    else  // indicate no document content.
                    {
                       byte [] content = "<?xml version = '1.0'?> <NO_DOCUMENT_CONTENT/>".getBytes();
                       xdoc.putContent(content,
                               NodeDocumentContentConverter.CONTENT_TYPE_ATTACHMENT); 
                    }
                         
                    responseDocumentsArray[i] = xdoc;
                         
                 } // end of for loop;
                 rs.close();
                 ps.close();
                 dbConnection.commit();
                 dbConnection.close();

                 //last but definetly not least insert this into the document holder
                 //this line of code handles the return of the node documents to the
                 // requestor by using functionality provided by CSC.
                 documents.value = responseDocumentsArray;
              }

           }  // isAuthorized block
           else
           {
              throw new Exception("User not Authorized for this Service.");
           }
        }// validate token block
     }
     catch  (Exception e)
     {
        //setServiceStatus(serviceTrackingId,e.getMessage());  
        try
        {
           dbConnection.rollback();
           servicesTracking.setTrackingStatus(e.getMessage());
           servicesTracking.writeToDB(dbConnection);
           if (rs != null)
           {
              rs.close();
           }
           if (ps != null)
           {
              ps.close();
           }
           if (dbConnection != null)
           {
             dbConnection.close();
           }
        }
        catch (Exception ex)
        {
         }
        faultString = e.getMessage();
        AxisFault fault = FaultHandler.getFault("Server", faultString, FaultErrorCode.INTERNAL_ERROR);
        throw fault;
     }  // end of outer catch block
  }


  public String query(String securityToken, String request, BigInteger rowId, BigInteger maxRows, String[] parameters) throws RemoteException
  {  
     String result_str = null;
     String servTrakId = null;
     String userId = null;
     try
     {
       getNodeInService();       
     }
     catch (Exception e)
     {
     }
     URL xslURL;
     WebServicesTracking servicesTracking = null;
     int i,j;
     Connection dbConnection = null;
     OracleCallableStatement cstmt = null;
     String serviceId= "3";

     try
     {
        if  (this.validateToken(securityToken, this.getClientHost()))
        {
           dbConnection = createDBConnection(db);
           dbConnection.setAutoCommit(false);
           
           //pull out the userId using the token.
           userId = getUser(securityToken);
           servicesTracking = new WebServicesTracking(userId,serviceId,"REQUESTED",dbConnection);
           servicesTracking.writeToDB(dbConnection);

           // if the user not authorized, Exception thrown in isAuthorize method.
           if (isAuthorize(userId, serviceId))
           {
              String sqlprocedure = getSQLProcedure(request);
              cstmt = (OracleCallableStatement) dbConnection.prepareCall(sqlprocedure);
              j = 0; 
              for (i=0 ; i<parameters.length ; i++)
              {          
                 if (parameters[i] != null && parameters[i].length()>0)
                 {
                    cstmt.setString(j+1 ,parameters[i]);
                    j++;
                 }
              }

              // if the rowId is null, set the first value for rowId to zero.
              if (rowId == null )
                cstmt.setInt(j+1,0);
              else
                cstmt.setInt(j+1,rowId.intValue());
              j++;
              // if a null is passed, set maxRows parameter to zero 
              if (maxRows == null)
                 cstmt.setInt(j+1,0);
              else
                 cstmt.setInt(j+1,maxRows.intValue());
              j++;
              cstmt.registerOutParameter((j+1),OracleTypes.CLOB);
              cstmt.execute();
              CLOB resultCLOB = cstmt.getCLOB(j+1);
              Reader inputStream = resultCLOB.getCharacterStream();
              StringBuffer sb = new StringBuffer("");
              char[] buffer = new char[1];
              int length = 0 ;
              while ((length = inputStream.read(buffer))!= -1 )
              {
                 sb.append(buffer);
              }
              result_str = sb.toString();
           
              resultCLOB.freeTemporary();
              cstmt.close();
              servicesTracking.setTrackingStatus("SUCCEED");
              servicesTracking.writeToDB(dbConnection);
              dbConnection.commit();
              dbConnection.close();
              return result_str;
           } // isAuthorize if statement
           else
           {
              throw new Exception("User not Authorized for this Service.");
           }
        }// validate token statement
     } // end of the try block
     catch (Exception e)
     {
        //setServiceStatus(servTrakId,"FAILED");
        try 
        {
           dbConnection.rollback();
           servicesTracking.setTrackingStatus("FAILED");
           servicesTracking.writeToDB(dbConnection);
           if (cstmt != null)
           {
             cstmt.close();
           }
           if (dbConnection != null)
           {
             dbConnection.close();
           }
        }
        catch (Exception e1 )
        {
        }
        faultString = e.getMessage();
        AxisFault fault = FaultHandler.getFault("Server", faultString, FaultErrorCode.QUERY);
        throw fault;
     }// end of catch block.
      
     return null;
  }

    
  public String execute(String authToken, String request, String[] parameters) throws RemoteException
  {
     return "Execute not supported.";
  }

    
  public String nodePing(String hello) throws RemoteException
  {
     if (inservice == true)
     {  
        return ("Ready");
     }
     else
     {
        return ("Unavaliable");
     }
  }

    
  public String[] getServices(String securityToken, String serviceType) throws RemoteException
  {
     Connection dbConnection= null;
     PreparedStatement ps=null;
     ResultSet rs = null;
     String serviceTrackingId = null;
     String userId;
     String serviceId = "4";
     WebServicesTracking servicesTracking = null;

     //check to see if the node is in service.
     try
     {
        getNodeInService();       
     }
     catch (Exception e)
     {
     }

     try
     {
        // check to see if the users token is valid, if it is proceed.
        if (this.validateToken(securityToken, this.getClientHost()))
        {
           // get a connection to the database.
           dbConnection = createDBConnection(db);
           dbConnection.setAutoCommit(false);
           
           //pull out the userId using the token.
           userId = getUser(securityToken);
           // log user has called the web service
           servicesTracking = new WebServicesTracking(userId,serviceId,"REQUESTED",dbConnection);
           servicesTracking.writeToDB(dbConnection);

           // if the user not authorized, Exception thrown in isAuthorize method.
           if (isAuthorize(userId, "4"))
           {
              // if the input parameter (serviceType) is "ServiceTypes" show all the
              // service types available.
              if (serviceType.compareToIgnoreCase("ServiceType") == 0)
              {
                 String sql = "SELECT UNIQUE service_type from services";
                 ps = dbConnection.prepareStatement(sql);
                 rs = ps.executeQuery();
                 Vector v = new Vector();
                 while (rs.next())
                 {
                    v.addElement(rs.getString("SERVICE_TYPE"));
                 }
                 int vsize = v.size();
                 String[] result_array = new String[vsize];
                 for (int i=0; i < vsize ; i++ )
                 {
                    result_array[i] = (String) v.get(i);
                 }
                 rs.close();
                 ps.close();
                 servicesTracking.setTrackingStatus("SUCCEED");
                 servicesTracking.writeToDB(dbConnection);
                 dbConnection.commit();
                 dbConnection.close();
                 return result_array;
              }
              // if the input parameter (serviceType) != "ServiceTypes" show all the
              // service types available for that particular type of service.
              else 
              {
                 // prepare and execute a prepared statement.
                 String sql = "SELECT service_name from services where service_type = ?";
                 ps = dbConnection.prepareStatement(sql);
                 ps.setString(1,serviceType);
                 rs = ps.executeQuery();
                 Vector v = new Vector();
                 // add the returned values into the vector.
                 while (rs.next())
                 {
                    v.addElement(rs.getString("SERVICE_NAME"));
                 }
                 // a java datatype Vector is not something that is understood in all
                 // environments.  Therefore, the vector elements are copied over into
                 // a string array.
                 int vsize = v.size();
                 String[] result_array = new String[vsize];
                 for (int i=0; i < vsize ; i++ )
                 {
                    result_array[i] = (String) v.get(i);
                 }
                 if (vsize <1) // if no results let the user know.
                 {
                    result_array = new String[1];
                    result_array[0] = "NO RESULTS";
                 }
                 // close all statements and database connections.
                 rs.close();
                 ps.close();
                 servicesTracking.setTrackingStatus("SUCCEED");
                 servicesTracking.writeToDB(dbConnection);
                 dbConnection.commit();
                 dbConnection.close();
                 //send back the string array populated with the results of the query.
                 //setServiceStatus(servTrakId,"SUCCESSFUL");
                 return result_array;
              }
           } // isAuthorize if statement
           else
           {
             throw new Exception("User not Authorized for this Service.");
           }
        } // validate if statement
     } // try block
     catch  (Exception e)
     {
        try
        {
          servicesTracking.setTrackingStatus(e.getMessage());
          servicesTracking.writeToDB(dbConnection);
          if (rs != null)
          {
            rs.close();
          }
          if (ps != null)
          {
            ps.close();
          }
          if (dbConnection != null)
          {
            dbConnection.close();
          }
        }
        catch (Exception ex)
        {
        }
        faultString = e.getMessage();
        AxisFault fault = FaultHandler.getFault("Server", faultString,
        FaultErrorCode.INTERNAL_ERROR);
        throw fault;
     }  // end of outer catch block
     return null; 
    }


  public String solicit(String securityToken, String returnURL, String request, String[] parameters) throws RemoteException
  {
     log.info("[solicit]: Enter.");
     Connection dbConnection= null;
     String userId = null;
     String serviceId = new String("12");
     String status = new String("Validated");
     String serviceTrackingId = null;
     String transactionId = null;
     String sqlStatement = null;
     StringBuffer pars  = new StringBuffer(578);
     PreparedStatement ps = null;
     WebServicesTracking servicesTracking = null;
     Transaction serverTransaction = null;

     try 
     {
        if (this.validateToken(securityToken, this.getClientHost())) // check users token, if valid continue.
        {  
           dbConnection=createDBConnection(db);
           dbConnection.setAutoCommit(false);

           //pull out the user Id matched with the token.
           userId = getUser(securityToken);
           //log in Service_Tracking table that user has called the Solicit method.
           servicesTracking = new WebServicesTracking(userId,serviceId,"REQUESTED",dbConnection);
           servicesTracking.writeToDB(dbConnection);
           serviceTrackingId = servicesTracking.getTrackingId();

           // if the user not authorized, Exception thrown in isAuthorize method.
           if (isAuthorize(userId, serviceId))
           {
              // next method will return an error if the requested query doesn't exist.
              sqlStatement = getSQLProcedure(request);
             
              GUID idGen = new GUID();
              serverTransaction = new Transaction();
              serverTransaction.setServiceTrackingId(serviceTrackingId);
              serverTransaction.setServerTransactionId(idGen.getGUID());
              serverTransaction.setStatus("PROCESSING");
              serverTransaction.setUserId(userId);
              serverTransaction.writeToDB(dbConnection);
             
              transactionId = serverTransaction.getServerTransactionId();
              // insert into incoming solicit table 
              String sql = "INSERT into  incoming_solicit "+
                           "values (?, ?,?,?, sysdate)";
              ps = dbConnection.prepareStatement(sql);
              ps.setString(1,transactionId);
              ps.setString(2,userId);
              ps.setString(3,request);
              ps.setString(4,returnURL);
              ps.execute();

              //put parameters in table 
              for (int i=0 ; i<parameters.length ; i++)
              {
                 sql = "INSERT into incoming_solicit_parameters (server_transaction_id, parameter) values (?, ?)";
                 ps = dbConnection.prepareStatement(sql);
                 ps.setString(1,transactionId);
                 ps.setString(2,parameters[i]);
                 ps.execute();
              } //end of loop to insert the parameter 

              // enqueue to the PENDING_JOB QUEUE
             
              // close all statements and database connections.
              ps.close();
              dbConnection.commit();
            
              serverTransaction.setStatus("RECEIVED");
              serverTransaction.updateToDB(dbConnection);
                         
              servicesTracking.setTrackingStatus("SUCCEED");
              servicesTracking.writeToDB(dbConnection);
            

              dbConnection.close();
              transactionId = serverTransaction.getServerTransactionId();

              log.info("[solicit]: Exit.");
              return transactionId;
           }  // isAuthorized block.
           else
           {
             throw new Exception("User not authorized for service.");
           }
        } // validate <token> block.
       
     } //end of the try block
     catch (Exception e)
     {
        try
        {
          dbConnection.rollback();
          servicesTracking.setTrackingStatus(e.getMessage());
          servicesTracking.writeToDB(dbConnection);
          serverTransaction.setStatus("FAILED");
          serverTransaction.updateToDB(dbConnection);
          if (ps != null)
          {
            ps.close();
          }
          if (dbConnection != null)
          {
            dbConnection.close();
          }
        }
        catch (Exception ex)
        {
        }
           faultString = e.getMessage();
           AxisFault fault = FaultHandler.getFault("Server", faultString, FaultErrorCode.INTERNAL_ERROR);
           throw fault;
     }
     return transactionId;
  }


 //****************************************************************//
 //                         HELPER METHODS
 //****************************************************************//

  private java.lang.String getClientHost()
  {
     // Get clienthost from the Axis Servlet Request
     org.apache.axis.MessageContext msgContext = org.apache.axis.MessageContext.getCurrentContext();
     HttpServletRequest req = (HttpServletRequest) msgContext.getProperty(HTTPConstants.MC_HTTP_SERVLETREQUEST);
     String clientHost = req.getRemoteAddr();
     log.debug("[getclienthost]: clienthost = " + clientHost);
     return clientHost;
  }

  /*
   * use NAAS to verify that the users' token is a valid one.
   */
  private boolean validateToken(String authToken, String clientHost) throws AxisFault
  {
     boolean valid = false;
     try
     {
        valid = SecurityMgr.isTokenValid(authToken, clientHost);
     }
     catch (RemoteException ex)
     {
        //something went wrong in connecting to NAAS
        AxisFault fault = FaultHandler.getFault("Unable to validate the security token, because of a probleming connecting to NAAS",
        "Unable to connect to NAAS.", FaultErrorCode.INTERNAL_ERROR);
        throw fault;
     }

     if (!valid)
     {
        AxisFault fault = FaultHandler.getFault("Invalid or Expired authentication Token Provided.",
                    "Authentication failure.", FaultErrorCode.INVALID_TOKEN);
        throw fault;
     }
     else
       return true;
  }


  /*
   * The getSQLProcedure takes in a string which will be the query statement to search
   * for.  The String returned should be the Stored Procedure to call against
   * the data base, in the correct format to be used in a OracleCallableStatement.
   */
  public String getSQLProcedure(String requests) throws AxisFault
  {
     int serviceid;
     String sqlquery;
     Connection dbConnection=null;
     PreparedStatement ps = null;
     ResultSet rs = null;
     try
     {
        //get a connection to the database.
        dbConnection = createDBConnection(db);
        String lowerRequest = requests.toLowerCase();
        String sql = "SELECT SERVICE_ID, SQL_STATEMENT FROM SERVICES WHERE LOWER(SERVICE_NAME)= ?"  ;
        // setup and execute a prepared statement.
        ps = dbConnection.prepareStatement(sql);
        ps.setString(1,lowerRequest);
        rs = ps.executeQuery();
        if  (rs.next() == false)
        {
           throw new Exception("The request method is not supported " + requests);          
        }
        // if results are returned means that there is a PL/SQL method which can be
        // called using the returned string.
        else 
        {
           sqlquery = rs.getString("SQL_STATEMENT");
        }
        // send the query string back to the calling method.
        return sqlquery;
     }// end of try block
     catch (Exception e)
     {
        try 
        {
           if (rs != null)
           {
             rs.close();;
           }
           if (ps != null)
           {
             ps.close();
           }
           if (dbConnection != null)
           {
             dbConnection.close();
           }
        }
        catch (Exception ex)
        {
        }
        AxisFault fault = FaultHandler.getFault("Server", "SQL procedure not found.",
                                              FaultErrorCode.INTERNAL_ERROR);
        throw fault;
     }
  }


  /*
   * The method getUser is a local method to find out the user of the token passed in.
   */
  public String getUser(String token) throws AxisFault
  {
     Connection dbConnection = null;
     OracleCallableStatement cstmt = null;
     try 
     {
        //get a connection to the database.
        dbConnection = createDBConnection(db);
        dbConnection.setAutoCommit(false);
        // setup and execute the call to the PL/SQL stored procedure.
        cstmt = (OracleCallableStatement) dbConnection.prepareCall("{call USER_AUTH.getuser(?,?)}");
        cstmt.setString(1,token);
        cstmt.registerOutParameter(2,Types.VARCHAR);
        cstmt.execute();
        String userId = cstmt.getString(2);
        dbConnection.commit();
        cstmt.close();
        // the call to the database has been closed.
        // commit and close the database connection.
        dbConnection.close();
        // if no userId returned, throw the appropriate exception (error).
        if  (userId  == null)
        {
           throw new Exception("User authentication failed");
        }
        return userId;
     }
     catch (Exception e)
     {
        try 
        {
           dbConnection.rollback();
           if (cstmt != null)
           {
             cstmt.close();
           }
           if (dbConnection != null)
           {
             dbConnection.close();
           }
        }
         catch (Exception e1)
         {
         }
         AxisFault fault = FaultHandler.getFault("Server", "user not available.", FaultErrorCode.INTERNAL_ERROR);
         throw fault;
     }
  }


  /*
   * The method isAuthorize is a local method used to check if the user 
   * is authorized to use a particular web service.
   */
  public boolean isAuthorize(String userId, String serviceId) throws AxisFault
  {
     Connection dbConnection = null;
     OracleCallableStatement cstmt = null;
     try 
     {
        //get a connection to the database.
        dbConnection = createDBConnection(db);
        dbConnection.setAutoCommit(false);
        // setup and execute the call to the PL/SQL stored procedure.
        cstmt = (OracleCallableStatement) dbConnection.prepareCall("{call USER_AUTH.authorize(?,?,?)}");
        cstmt.setString(1,userId);
        cstmt.setString(2,serviceId);
        cstmt.registerOutParameter(3,Types.INTEGER);
        cstmt.execute();
        int returnFlag = cstmt.getInt(3);
        dbConnection.commit();
        cstmt.close();
        // the call to the database has been closed.
        // close the database connection.
        dbConnection.close();
        // return boolean value based on the database flag.
        if  (returnFlag  == 1  )
           return false;
        //throw new Exception("User not authorized for this service.");
        else 
           return true;
     }
     catch (Exception e) // deal with any errors that come up.
     {
        try 
        {
           dbConnection.rollback();
           if (cstmt != null)
           {
             cstmt.close();
           }     
           if (dbConnection != null)
           {
             dbConnection.close();
           }     
        }
        catch (Exception e1)
        {
        }
        AxisFault fault = FaultHandler.getFault("Server", "user not authorized", FaultErrorCode.INTERNAL_ERROR);
        throw fault;
     }
  }


  public Connection createDBConnection(String source) throws AxisFault
  {
     Connection dbConnection = null;
     try
     {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        dbConnection = DriverManager.getConnection(connectString, "<schema_user>", "<user_password>");
     }
     catch (Exception e)
     {
        faultString = e.getMessage();
        AxisFault fault = FaultHandler.getFault("Server", faultString, FaultErrorCode.INTERNAL_ERROR);
        throw fault;
     }
     return dbConnection;
  }

  /*
   * The method setNodeInService is used to change the status of the node.
   */
  public void setNodeInService(boolean switchflag )
  {
     inservice = switchflag;
  }

  /*
   * The method getNodeInService is used to check the status of the Node.
   */
  public boolean getNodeInService() throws AxisFault
  {
     if (inservice == true ) 
       return true;
     else
     {
        AxisFault fault = FaultHandler.getFault("Server", "Node is Unavailable", 
          FaultErrorCode.SERVER_BUSY);
        throw fault; //Exception("Node is Unavailable");
     }
  }

}// End of Node class file.