/**
 * Copyright © 2001 The JA-SIG Collaborative.  All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 *
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in
 *    the documentation and/or other materials provided with the
 *    distribution.
 *
 * 3. Redistributions of any form whatsoever must retain the following
 *    acknowledgment:
 *    "This product includes software developed by the JA-SIG Collaborative
 *    (http://www.jasig.org/)."
 *
 * THIS SOFTWARE IS PROVIDED BY THE JA-SIG COLLABORATIVE "AS IS" AND ANY
 * EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE JA-SIG COLLABORATIVE OR
 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
 * NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
 * OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 */

package org.jasig.portal;

import org.jasig.portal.Applicant;
import org.jasig.portal.UserInstitution;
import org.jasig.portal.services.EntityCachingService;
import org.jasig.portal.services.Authentication;
import org.jasig.portal.security.Md5;
import org.jasig.portal.security.PersonFactory;
import org.jasig.portal.security.IPerson;
import org.jasig.portal.security.PortalSecurityException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jasig.portal.RDBMServices;
import java.util.ArrayList;
import java.util.List;


/**
 * <p>A class that saves and retrieves applicants and their educational institutions to/from the portal database.</p>
 *
 * @author rtwigg@uccs.edu
 */
public class RDBMApplicantStore implements IApplicantStore {

  private static final Log log = LogFactory.getLog(RDBMApplicantStore.class);
//  String defaultApplicantuserName = //PropertiesManager.getProperty("org.jasig.portal.security.PersonManagerFactory.implementation");
   private static String APPLICANT_USER_NAME = "gapplicant";
  /**
   * Get an applicant.
   * @param userName a user name
   * @return applicant, an applicant or <code>null</code> if no matching applicant is found.
   * @throws java.sql.SQLException
   */

  public Applicant getApplicant(String userName) throws SQLException {
    Applicant applicant = null;
    
    // Check the cache
    try {
      applicant = (Applicant)EntityCachingService.instance().get(Applicant.class, userName);
    } catch (Exception e) {
      log.error("Error checking cache for applicant with user name " + userName, e);
    }
    
    // If not found in cache, get it from the store and cache it, otherwise return it
    if (applicant == null) {
      Connection conn = null;
      RDBMServices.PreparedStatement pstmtApplicant = null;
      RDBMServices.PreparedStatement pstmtUserData = null;
      RDBMServices.PreparedStatement pstmtInstitutions = null;
      ResultSet rs = null;
      ResultSet rs1 = null;
      ResultSet rs2 = null;
      String institutionName = null;
  
      try {
        conn = RDBMServices.getConnection();
        pstmtApplicant = getApplicantPstmt(conn);
        pstmtInstitutions = getInstitutionsPstmt(conn);
        pstmtInstitutions.clearParameters();
        pstmtApplicant.setString(1, userName);
        log.debug("RDBMApplicantStore.getApplicant(): " + pstmtApplicant);
        rs = pstmtApplicant.executeQuery();
        if (rs.next()) {
          if (applicant==null) {
            applicant = new Applicant(userName);
          }
          applicant.setStatus(rs.getInt(1));
//          rs.close();
          pstmtUserData = getUserDataPstmt(conn);
          pstmtUserData.clearParameters();
          pstmtUserData.setString(1, userName);
          rs1 = pstmtUserData.executeQuery();
          if (rs1.next()) {
            applicant.setFirstName(rs1.getString(1));
            applicant.setLastName(rs1.getString(2));
            applicant.setEmail(rs1.getString(3));
          }
//          rs1.close(); 
          pstmtInstitutions.clearParameters();
          pstmtInstitutions.setString(1, userName);
          log.debug("RDBMApplicantStore.getApplicant(): " + pstmtInstitutions);
          rs2 = pstmtInstitutions.executeQuery();
          if (rs2.next()) {
            do {
              institutionName = rs2.getString(1);
              UserInstitution userInstitution = new UserInstitution(userName, institutionName);
              userInstitution.setMajor(rs2.getString(2));
              userInstitution.setDegree(rs2.getString(3));
              userInstitution.setGPA(rs2.getString(4));
              userInstitution.setTranscriptStatus(rs2.getString(5));
              applicant.addInstitution(userInstitution);
            } while (rs2.next());
          }
          log.debug("RDBMApplicantStore.getApplicant(): Read applicant " + userName + " from the store");
          // Add the applicant to the cache
          try {
            EntityCachingService.instance().add(applicant);
          } catch (Exception e) {
            log.error("Error caching applicant " + applicant, e);
          } 
        } // end if (rs.next())
      } finally {
        try { rs.close(); } catch (Exception e) {}
        try { rs1.close(); } catch (Exception e) {}
        try { rs2.close(); } catch (Exception e) {}
        try { pstmtApplicant.close(); } catch (Exception e) {}
        try { pstmtUserData.close(); } catch (Exception e) {}
        try { pstmtInstitutions.close(); } catch (Exception e) {}
        try { RDBMServices.releaseConnection(conn); } catch (Exception e) {}
      } // end try/finally
    } // end if applicant==null
    return applicant;
  }

  public Applicant[] getApplicants() throws SQLException {
    Applicant[] applicants = null;
    Connection conn = null;
    Applicant applicant = null;
 
    RDBMServices.PreparedStatement pstmtApplicants = null;
    RDBMServices.PreparedStatement pstmtUserData = null;
    RDBMServices.PreparedStatement pstmtInstitutions = null;
    ResultSet rs = null;
    ResultSet rs1 = null;
    ResultSet rs2 = null;
    String userName = null;

    try {
      conn = RDBMServices.getConnection();
      pstmtApplicants = getApplicantsPstmt(conn);
      log.debug("RDBMApplicantStore.getApplicants(): " + pstmtApplicants);
      rs = pstmtApplicants.executeQuery();
      List applicantList = new ArrayList();
      while (rs.next()) {
        userName = rs.getString(1);
        // Check the cache
        try {
          applicant = (Applicant)EntityCachingService.instance().get(Applicant.class, userName);
        } catch (Exception e) {
          log.error("Error checking cache for applicant with user name " + userName, e);
        }
        if (applicant == null)
        {
          applicant = new Applicant(userName);
          applicant.setStatus(rs.getInt(2));

          pstmtUserData = getUserDataPstmt(conn);
          pstmtUserData.clearParameters();
          pstmtUserData.setString(1, userName);
          rs1 = pstmtUserData.executeQuery();
          if (rs1.next()) {
            applicant.setFirstName(rs1.getString(1));
            applicant.setLastName(rs1.getString(2));
            applicant.setEmail(rs1.getString(3));
          } 

          // Get user institutions for each applicant
          pstmtInstitutions = getInstitutionsPstmt(conn);
          pstmtInstitutions.clearParameters();
          pstmtInstitutions.setString(1, userName);
          log.debug("RDBMApplicantStore.getApplicants(): " + pstmtInstitutions);
          rs2 = pstmtInstitutions.executeQuery();
          if (rs2.next()) {
            do {
              UserInstitution userInstitution = new UserInstitution(userName, rs2.getString(2));
              userInstitution.setID(Integer.toString(applicant.getNextuiID()));
              userInstitution.setMajor(rs2.getString(3));
              userInstitution.setDegree(rs2.getString(4));
              userInstitution.setGPA(rs2.getString(5));
              userInstitution.setTranscriptStatus(rs2.getString(6));
              applicant.addInstitution(userInstitution);
            } while (rs2.next());
          }
          log.debug("RDBMApplicantStore.getApplicants(): Read applicant " + userName + " from the store");
          // Add the applicant to the cache
          try {
            EntityCachingService.instance().add(applicant);
          } catch (Exception e) {
            log.error("Error caching applicant " + applicant, e);
          } 
        } //end if applicant==null

        applicantList.add(applicant);

      } // end while (rs.next())
      applicants = (Applicant[])applicantList.toArray(new Applicant[0]);
    } finally {
        try { rs.close(); } catch (Exception e) {}
        try { rs1.close(); } catch (Exception e) {}
        try { rs2.close(); } catch (Exception e) {}
        try { pstmtApplicants.close(); } catch (Exception e) {}
        try { pstmtInstitutions.close(); } catch (Exception e) {}
        try { RDBMServices.releaseConnection(conn); } catch (Exception e) {}
    } // end try/finally
    return applicants;
  }

  public Applicant newApplicant(String firstName, String lastName) throws Exception {
    String userName = newUserName(firstName, lastName);
    Applicant applicant = new Applicant(userName);
    return applicant;
  }


  /*
   * Returns a unique user name, first trying first initial of first name + last name, then    * concatentating incremental integers until unique user name is found in UP_PERSON_DIR.
   *
   */ 
  public String newUserName(String firstName, String lastName) throws SQLException {

      Connection conn = null;
      RDBMServices.PreparedStatement pstmtUserName = null;
      ResultSet rs = null;
      String userName, comboName = null;
  
      try {
        conn = RDBMServices.getConnection();
        pstmtUserName = getUserNamePstmt(conn);
        pstmtUserName.clearParameters();
        comboName = firstName.substring(0, 1) + lastName; 
        userName = comboName.toLowerCase();
        pstmtUserName.setString(1, userName);
        rs = pstmtUserName.executeQuery();
        if (rs.next()) {
           int i=0;
           do {
             i++;
             pstmtUserName.clearParameters();
             comboName=firstName.substring(0, 1) + lastName + Integer.toString(i);
             userName=comboName.toLowerCase();
             pstmtUserName.setString(1, userName);
             rs = pstmtUserName.executeQuery();
           } while (rs.next());
        } // end if (rs.next())
      } finally {
          try { rs.close(); } catch (Exception e) {}
          try { pstmtUserName.close(); } catch (Exception e) {}
          try { RDBMServices.releaseConnection(conn); } catch (Exception e) {}
      } // end try/finally
    return userName;
  }


  public void putApplicant(Applicant applicant) throws SQLException {

    RDBMServices.PreparedStatement pstmtAppQuery = null;
    RDBMServices.PreparedStatement pstmtAppInsert = null;
    RDBMServices.PreparedStatement pstmtAppUpdate = null;
    RDBMServices.PreparedStatement pstmtUserQuery = null;
    RDBMServices.PreparedStatement pstmtUserInsert = null;
    RDBMServices.PreparedStatement pstmtUserUpdate = null;
    RDBMServices.PreparedStatement pstmtInstDelete = null;
    RDBMServices.PreparedStatement pstmtInstInsert = null;
    ResultSet rs = null;
    String sqlmd5pw = null;

    String sqlUserName = RDBMServices.sqlEscape(applicant.getUserName());
    String sqlPasswd = applicant.getPasswd();
    try { sqlmd5pw = Md5.encryptPasswd(sqlPasswd); } catch (Exception e) {}
    String sqlFirstName = RDBMServices.sqlEscape(applicant.getFirstName());
    String sqlLastName = RDBMServices.sqlEscape(applicant.getLastName());
    String sqlEmail = RDBMServices.sqlEscape(applicant.getEmail());
    String sqlInstName = null;
    String sqlMajor = null;
    String sqlDegree = null;
    String sqlGPA = null;
    String sqlTranscriptStatus = null;

    RDBMServices rdbm = new RDBMServices();
    Connection conn = RDBMServices.getConnection();
    pstmtAppQuery = getAppQueryPstmt(conn);
    try 
    {
       // Set autocommit false for the connection
       RDBMServices.setAutoCommit(conn, false);
       pstmtAppQuery.clearParameters();
       pstmtAppQuery.setString(1, sqlUserName);
       rs = pstmtAppQuery.executeQuery();  
       log.info("RDBMApplicantStore.putApplicant(): " + pstmtAppQuery);
       // If username exists in UP_APPLICANT, do an update, otherwise do an insert
       if (rs.next()) 
       {
           pstmtAppUpdate = getAppUpdatePstmt(conn);
           pstmtAppUpdate.clearParameters();
           pstmtAppUpdate.setInt(1, applicant.getStatus());
           pstmtAppUpdate.setString(2, sqlUserName);
           pstmtAppUpdate.executeUpdate();
       }  
       else 
       {
           pstmtAppInsert = getAppInsertPstmt(conn);
           pstmtAppInsert.clearParameters();
           pstmtAppInsert.setString(1, sqlUserName);
           pstmtAppInsert.setInt(2, applicant.getStatus());
           log.info("RDBMApplicantStore.putApplicant(): " + pstmtAppInsert);
           pstmtAppInsert.executeUpdate();
       }
       rs.close();
       // Now update/insert into UP_PERSON_DIR
       pstmtUserQuery = getUserQueryPstmt(conn);
       pstmtUserQuery.clearParameters();
       pstmtUserQuery.setString(1, sqlUserName);
       rs = pstmtUserQuery.executeQuery();  
       log.info("RDBMApplicantStore.putApplicant(): " + pstmtUserQuery);
       // If username exists in UP_PERSON_DIR, do an update, otherwise do an insert
       if (rs.next()) 
       {
           pstmtUserUpdate = getUserUpdatePstmt(conn);
           pstmtUserUpdate.clearParameters();
           pstmtUserUpdate.setString(1, sqlFirstName);
           pstmtUserUpdate.setString(2, sqlLastName);
           pstmtUserUpdate.setString(3, sqlEmail);
           pstmtUserUpdate.setString(4, sqlUserName);
           pstmtUserUpdate.executeUpdate();
           rs.close();
       } 
       else
       {
           pstmtUserInsert = getUserInsertPstmt(conn);
           pstmtUserInsert.clearParameters();
           pstmtUserInsert.setString(1, sqlUserName);
           pstmtUserInsert.setString(2, "(MD5)"+sqlmd5pw);
           pstmtUserInsert.setString(3, sqlFirstName);
           pstmtUserInsert.setString(4, sqlLastName);
           pstmtUserInsert.setString(5, sqlEmail);
           pstmtUserInsert.executeUpdate();
           rs.close();

           // Create a portal user 
           IPerson person = null;
           try {
              // Create a guest person
              person = PersonFactory.createGuestPerson();
           } catch (Exception e) {
           // Log the exception
             log.error("Exception creating guest person.", e);
           }
           person.setAttribute(IPerson.USERNAME, applicant.getUserName());
           person.setAttribute("uPortalTemplateUserName", APPLICANT_USER_NAME);
           Authentication au = new Authentication();   
           try {
             au.setUID(person);
           } catch (PortalSecurityException e) {
             log.error("RDBMApplicantStore::putApplicant: Exception setting user id.", e);
           }
       }
     
       // Delete any existing institution records for this user/applicant
       pstmtInstDelete = getInstDeletePstmt(conn);
       pstmtInstDelete.clearParameters();
       pstmtInstDelete.setString(1, sqlUserName);
       log.info("RDBMApplicantStore.putApplicant(): " + pstmtInstDelete);
       pstmtInstDelete.executeUpdate();
       log.info("RDBMApplicantStore.putApplicant(): inst recs deleted");

       // Add institution records for this user/applicant
       UserInstitution[] userInst = applicant.getUserInstitutions();

       if (userInst != null) {     
         pstmtInstInsert = getInstInsertPstmt(conn);
         for (int i = userInst.length-1; i >= 0; i--) {
           UserInstitution userInstitution = userInst[i];
           sqlInstName = RDBMServices.sqlEscape(userInst[i].getInstitutionName());
           sqlMajor = RDBMServices.sqlEscape(userInst[i].getMajor());
           sqlDegree = RDBMServices.sqlEscape(userInst[i].getDegree());
           sqlGPA = RDBMServices.sqlEscape(userInst[i].getGPA());
           sqlTranscriptStatus = RDBMServices.sqlEscape(userInst[i].getTranscriptStatus());
           pstmtInstInsert.clearParameters();
           pstmtInstInsert.setString(1, sqlUserName);
           pstmtInstInsert.setString(2, sqlInstName);
           pstmtInstInsert.setString(3, sqlMajor);
           pstmtInstInsert.setString(4, sqlDegree);
           pstmtInstInsert.setString(5, sqlGPA);
           pstmtInstInsert.setString(6, sqlTranscriptStatus);
           log.info("RDBMApplicantStore.putApplicant(): " + pstmtInstInsert);
           pstmtInstInsert.executeUpdate();
         }
       }
       // Commit the transactions
       RDBMServices.commit(conn);
       try {
//         EntityCachingService.instance().update(applicant);
         EntityCachingService.instance().remove(Applicant.class, sqlUserName);
         EntityCachingService.instance().add(applicant);
       } catch (Exception e) {
         log.error("Error caching applicant " + applicant, e);
       } 
     } // end try    
     catch (SQLException sqle) 
     {
       log.error("Exception saving applicant" + sqlUserName, sqle);
       RDBMServices.rollback(conn);
       throw sqle;
     } finally {
       try { rs.close(); } catch (Exception e) {}
       try { pstmtUserQuery.close(); } catch (Exception e) {}
       try { pstmtUserUpdate.close(); } catch (Exception e) {}
       try { pstmtUserInsert.close(); } catch (Exception e) {}
       try { pstmtInstDelete.close(); } catch (Exception e) {}
       try { pstmtInstInsert.close(); } catch (Exception e) {}
       try { RDBMServices.releaseConnection(conn); } catch (Exception e) {}
     } // end try/finally
  }

  public void updatePersonMsg(String userName, String msg) throws SQLException {

    RDBMServices.PreparedStatement pstmtUserQuery = null;
    RDBMServices.PreparedStatement pstmtMsgUpdate = null;
    ResultSet rs = null;

    String sqlUserName = RDBMServices.sqlEscape(userName);
    String sqlMsg = RDBMServices.sqlEscape(msg);

    RDBMServices rdbm = new RDBMServices();
    Connection conn = RDBMServices.getConnection();

    // Update msg in UP_PERSON_DIR
    try 
    {
       // Set autocommit false for the connection
       RDBMServices.setAutoCommit(conn, false);
       pstmtUserQuery = getUserQueryPstmt(conn);
       pstmtUserQuery.clearParameters();
       pstmtUserQuery.setString(1, sqlUserName);
       rs = pstmtUserQuery.executeQuery();  
       log.info("RDBMApplicantStore.updatePersonMsg(): " + pstmtUserQuery);
       // If username exists in UP_PERSON_DIR do an update, else it's an error
       if (rs.next()) 
       {
           pstmtMsgUpdate = getMsgUpdatePstmt(conn);
           pstmtMsgUpdate.clearParameters();
           pstmtMsgUpdate.setString(1, sqlMsg);
           pstmtMsgUpdate.setString(2, sqlUserName);
           pstmtMsgUpdate.executeUpdate();
           rs.close();
       } 

       // Commit the transactions
       RDBMServices.commit(conn);

     } // end try    
     catch (SQLException sqle) 
     {
       log.error("Exception updating message for " + sqlUserName, sqle);
       RDBMServices.rollback(conn);
       throw sqle;
     } finally {
       try { rs.close(); } catch (Exception e) {}
       try { pstmtUserQuery.close(); } catch (Exception e) {}
       try { pstmtMsgUpdate.close(); } catch (Exception e) {}
       try { RDBMServices.releaseConnection(conn); } catch (Exception e) {}
     } // end try/finally

  }


  protected static final RDBMServices.PreparedStatement getUserDataPstmt(Connection conn) throws SQLException {

    String sql = "SELECT FIRST_NAME, LAST_NAME, EMAIL FROM UP_PERSON_DIR WHERE USER_NAME=?";

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getApplicantsPstmt(Connection conn) throws SQLException {

    String sql = "SELECT * FROM UP_APPLICANT";

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getUserNamePstmt(Connection conn) throws SQLException {

    String sql = "SELECT FIRST_NAME, LAST_NAME FROM UP_PERSON_DIR WHERE USER_NAME=?";

    return new RDBMServices.PreparedStatement(conn, sql);
  }


  protected static final RDBMServices.PreparedStatement getInstitutionsPstmt(Connection conn) throws SQLException {

    String sql = "SELECT * FROM UP_APP_INSTITUTION WHERE USER_NAME=?";

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getApplicantPstmt(Connection conn) throws SQLException {

    String sql = "SELECT APP_STATUS FROM UP_APPLICANT WHERE USER_NAME=?";

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getUserQueryPstmt(Connection conn) throws SQLException {

    String sql = "SELECT FIRST_NAME, LAST_NAME, EMAIL FROM UP_PERSON_DIR WHERE USER_NAME=?";

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getUserUpdatePstmt(Connection conn) throws SQLException {

    String sql = "UPDATE UP_PERSON_DIR SET FIRST_NAME = ?, LAST_NAME = ?, " + 
                 "EMAIL = ? WHERE USER_NAME = ?"; 

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getMsgUpdatePstmt(Connection conn) throws SQLException {

    String sql = "UPDATE UP_PERSON_DIR SET MESSAGE = ? WHERE USER_NAME = ?"; 
    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getUserInsertPstmt(Connection conn) throws SQLException {

    String sql = "INSERT INTO UP_PERSON_DIR (USER_NAME, ENCRPTD_PSWD, FIRST_NAME, " +
    "LAST_NAME, EMAIL) VALUES (?, ?, ?, ?, ?)";    

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getAppQueryPstmt(Connection conn) throws SQLException {

    String sql = "SELECT APP_STATUS FROM UP_APPLICANT WHERE USER_NAME=?";

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getAppUpdatePstmt(Connection conn) throws SQLException {

    String sql = "UPDATE UP_APPLICANT SET APP_STATUS = ? WHERE USER_NAME = ?"; 

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getAppInsertPstmt(Connection conn) throws SQLException {

    String sql = "INSERT INTO UP_APPLICANT (USER_NAME, APP_STATUS) VALUES (?, ?)";    

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getInstDeletePstmt(Connection conn) throws SQLException {

    String sql = "DELETE FROM UP_APP_INSTITUTION WHERE USER_NAME = ?";

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getInstInsertPstmt(Connection conn) throws SQLException {

    String sql = "INSERT INTO UP_APP_INSTITUTION (USER_NAME, INSTITUTION_NAME, " +
    "MAJOR, DEGREE, GPA, TRANSCRIPT_STATUS) VALUES (?, ?, ?, ?, ?, ?)";    

    return new RDBMServices.PreparedStatement(conn, sql);
  }

}