import java.io.*; 
import java.util.*; 
import java.sql.*; 
import javax.sql.*; 
import com.microsoft.jdbc.sqlserver.*;

public class Employee {
  protected PreparedStatement m_select;
  protected PreparedStatement m_insert;
  protected PreparedStatement m_update;
  protected PreparedStatement m_delete;
  protected Integer m_OID;
  protected String m_SocialInsuranceNumber;
  protected Double m_CurrentSalary;
  protected java.sql.Date m_HireDate;
  protected Integer m_OIDPersonalInfo;
  protected String m_Personal_InfoName;
  protected Integer m_rowsAffected;
  public static final String SELECT_SQL_FIELDS =
            "[Employee].[OID]," +
            "[Employee].[Social Insurance Number]," +
            "[Employee].[Current Salary]," +
            "[Employee].[Hire Date]," +
            "[Employee].[OID Personal Info]," +
            "[Personal Info].[Name]";
  public static final String SELECT_SQL_FROM =
       "{oj [Employee]" +
       "LEFT OUTER JOIN [Personal Info] ON "  +
       "[Employee].[OID Personal Info] = [Personal Info].[OID] "
       + "}";
  public static final String SELECT_SQL_WHERE =
      "[Employee].[OID] = ?;";
  public static final String SELECT_SQL =
    "SELECT " + SELECT_SQL_FIELDS +
    " FROM " + SELECT_SQL_FROM +
    " WHERE " + SELECT_SQL_WHERE;
  public static final String INSERT_SQL =
       "INSERT INTO [Employee] ( " +
            "[OID]," + // OID is not an identity column so it must be specified
            "[Social Insurance Number]," +
            "[Current Salary]," +
            "[Hire Date]," +
            "[OID Personal Info]" +
          ") VALUES (" + 
          "? ," + // placeholder for OID
          "? ,? ,? ,?);" + SELECT_SQL;
  public static final String UPDATE_SQL =
       "UPDATE [Employee] SET " +
            "[Social Insurance Number] = ?," +
            "[Current Salary] = ?," +
            "[Hire Date] = ?," +
            "[OID Personal Info] = ?" +
      " WHERE [OID] = ?;" + SELECT_SQL;
  public static final String DELETE_SQL =
      "DELETE [Employee] " +
      " WHERE [OID] = ?;";
  public Employee(Connection conn) throws SQLException {
    m_select = conn.prepareCall(SELECT_SQL);
    m_insert = conn.prepareCall(INSERT_SQL);
    m_update = conn.prepareCall(UPDATE_SQL);
    m_delete = conn.prepareCall(DELETE_SQL);
    clear();
  }
  public int getOID() {return m_OID.intValue();}
  public String getSocialInsuranceNumber() {return m_SocialInsuranceNumber;}
  public double getCurrentSalary() {return m_CurrentSalary.doubleValue();}
  public java.sql.Date getHireDate() {return m_HireDate;}
  public int getOIDPersonalInfo() {return m_OIDPersonalInfo.intValue();}
  public String getPersonal_InfoName() {return m_Personal_InfoName;}
  public int getRowsAffected() {return m_rowsAffected.intValue();}
  public void select(int v_OID) throws java.sql.SQLException {
    clear();
    m_select.clearParameters();
    m_select.setInt(1, v_OID);
    ResultSet rs = m_select.executeQuery();
    if (rs.next()) {
      copyFields(rs);
      int l_rowsAffected = 1;
      while (rs.next()) {l_rowsAffected++;}
      m_rowsAffected = new Integer(l_rowsAffected);
    }
    rs.close();
  }
  public void insert(int v_OID,
      String v_Social_Insurance_Number,
      double v_Current_Salary,
      java.util.Date v_Hire_Date,
      int v_OID_Personal_Info) throws java.sql.SQLException {
    clear();
    m_insert.clearParameters();
    m_insert.setInt(1, v_OID);
    m_insert.setString(2, v_Social_Insurance_Number);
    m_insert.setDouble(3, v_Current_Salary);
    m_insert.setDate(4, new java.sql.Date(v_Hire_Date.getTime()));
    m_insert.setInt(5, v_OID_Personal_Info);
    m_insert.setInt(6, v_OID);
    m_insert.execute();
    int l_rowsAffected = m_insert.getUpdateCount();
    if (l_rowsAffected > 0 ) { //Insert row succeeded
      if (m_insert.getMoreResults()) { // move to next result
        ResultSet rs = m_insert.getResultSet(); // fetch the "select" result
        rs.next();
        copyFields(rs);
        rs.close();
      }
      m_rowsAffected = new Integer(l_rowsAffected);
    }
  }
  public void update(int v_OID,
      String v_Social_Insurance_Number,
      double v_Current_Salary,
      java.util.Date v_Hire_Date,
      int v_OID_Personal_Info) throws java.sql.SQLException {
    clear();
    m_update.clearParameters();
    m_update.setString(1, v_Social_Insurance_Number);
    m_update.setDouble(2, v_Current_Salary);
    m_update.setDate(3, new java.sql.Date(v_Hire_Date.getTime()));
    m_update.setInt(4, v_OID_Personal_Info);
    m_update.setInt(5, v_OID);
    m_update.setInt(6, v_OID);
    m_update.execute();
    int l_rowsAffected = m_update.getUpdateCount();
    if (l_rowsAffected > 0 ) { //Insert row succeeded
      if (m_update.getMoreResults()) { // move to next result
        ResultSet rs = m_update.getResultSet(); // fetch the "select" result
        rs.next();
        copyFields(rs);
        rs.close();
      }
      m_rowsAffected = new Integer(l_rowsAffected);
    }
  }
  public void delete(int v_OID) throws java.sql.SQLException {
    clear();
    m_delete.clearParameters();
    m_delete.setInt(1, v_OID);
    m_rowsAffected = new Integer(m_delete.executeUpdate());
  }
  protected void copyFields(ResultSet rs) throws java.sql.SQLException {
    this.m_OID = new Integer(rs.getInt(1));
    this.m_SocialInsuranceNumber = rs.getString(2);
    this.m_CurrentSalary = new Double(rs.getDouble(3));
    this.m_HireDate = rs.getDate(4);
    this.m_OIDPersonalInfo = new Integer(rs.getInt(5));
    this.m_Personal_InfoName = rs.getString(6);
  }
  public void clear() {
    m_OID = null;
    m_SocialInsuranceNumber = null;
    m_CurrentSalary = null;
    m_HireDate = null;
    m_OIDPersonalInfo = null;
    m_Personal_InfoName = null;
    m_rowsAffected = null;
  }
  public void close() throws SQLException {
    m_select.close();
    m_insert.close();
    m_update.close();
    m_delete.close();
  }
}