import java.sql.*;
import java.util.*;
import java.io.*;
 
/* 
 * Location
 *
 * $Author: $
 * $Date: $
 * $Revisions: $
 * $Source: $
 *  To create a new Location
 * 
 *    Location instance = new Location
 *     .. call the set methods                       // Set the fields as needed
 *    String key = instance.store();                 // .store() inserts record to database
 *                                                   // DB sequence "location_seq" is used to get
 *                                                   // next primary key
 *
 *  To get a Location from the database
 *                                                       // To retrieve a Location from database
 *    Location instance = new Location("1234");              // pass the primary key to constructor
 *    ... call the get() methods                         // and then call the getter methods             
 * 
 *
 *  To update an existing Location                 
 *                                                        // To update an existing OrderObj
 *    Location instance = new Location("1234");               // get it from database, call the setter methods
 *     .. call the set methods                            // and then call store()
 *    instance.store(); 
 *
 *
 */
public class Location {

  private long key = 0;
  private String name = "";
  private long regionId = 0;
  public Region region  = null;
  public Division division  = null;


  private boolean isDirty = false;

  /**
   *
   * To get an existing Location call constructor with primary key
   *
   */
  public Location(long keyIn) {  
    Connection conn = null;
    Statement stmt = null;
    try {
     conn = getConnection();
     stmt = conn.createStatement();
    } catch (SQLException exp) {             
      log("Driver(key) constructor, Exception: " + exp.getMessage());
    } 

    key = keyIn;
    ResultSet rset = doSelect(conn, stmt);
    region = new Region(rset);
    division = new Division(rset);

    try {
      rset.close();
      stmt.close();
      conn.close();
    } catch (SQLException exp) {             
      log("Location(key) constructor, Exception: " + exp.getMessage());
    } 


  }


  /**
   *
   * To populate a Location where the sql select was done earlier by another object
   * call this constructor with the result set.
   *   
   */
  public Location(ResultSet rset) {  
    applyResultSet(rset);                         //Populate this object with values from db 
    region = new Region(rset);
    division = new Division(rset);

  }

  /***
   * To create a new Location call no args constructor 
   * then call the "set()" methods, and finally "store()"
   */
  public Location() {  
    region = new Region();
    division = new Division();

  }


  /**
   *
   * Persist our properties to the db.  Also call store() on any dependent objects.
   * If no Connection was passed in then we are responisible for beginning the transaction
   * Turn off auto commit, and rollback if we get exception from this or any dependent objects
   *
   */
  public long store() throws Exception { 
    long returnKey = key;
    Connection conn = getConnection();
    try { 
      conn.setAutoCommit(false); 
      log("Location.store(), Starting transaction");
      returnKey = store(conn);
      conn.commit();  
      log("Location.store(), No Exceptions Committing");
    }
    catch (Exception exp) {    //Any exception thrown by store causes a rollback. 
      log("Location.store(), Exception: " + exp.getMessage());
      try {                      
        log("Location.store(), Failed: RollingBack");
        conn.rollback();
      } catch (SQLException rollbackExp) {             
        log("Location.store(), Exception: " + rollbackExp.getMessage());
      } 
      throw exp;  //Throw exception back up so that user interface can report error
    }
    finally { 
      try {
        conn.close();
      } catch (SQLException closeExp) {             
        log("Location.store(), Exception: " + closeExp.getMessage());
      } 
    }
    return returnKey;

  }


  /**
   *
   * Persist our properties to the db.  Also call store() on any dependent objects.
   * If a Connection was passed in then a object higher up is handling the transactions 
   * for us. Throw on any exceptions so that the higher object can catch them and rollback. 
   * This method will do an insert for new Location and a update for existing ones    
   *
   */
  public long store(Connection conn) throws Exception { 
    long returnKey = key;
      if ( key == 0 ) {   
        returnKey = insertToDB(conn); 
      }
      else { 
        if ( isDirty ) { updateOnDB(conn); }
      }
    return returnKey;
  }


  /**
   *
   */
  private ResultSet doSelect(Connection conn, Statement stmt) { 
    ResultSet rset = null;

    try { 
      String sqlStmt = "select  \n" + 
      " location.location_id as location_location_id, \n" +
      " location.name as location_name, \n" +
      " location.region_id as location_region_id, \n" +
      " region.region_id as region_region_id, \n" +
      " region.name as region_name, \n" +
      " region.division_id as region_division_id, \n" +
      " division.division_id as division_division_id, \n" +
      " division.name as division_name  \n" + 
      " from location location, \n" + 
      " region region, \n" + 
      " division division    \n" +  
      " where location.location_id = " + key 
      + " and location.region_id = region.region_id    \n"
      + " and region.division_id = division.division_id    \n";
      log("sqlStmt=" + sqlStmt);
      rset = stmt.executeQuery(sqlStmt);
      rset.next();
      applyResultSet(rset);
    }
    catch (Exception exp) {
      log(".doSelect(), SQLException: " + exp.getMessage());
    }

   return rset;

  }


  /**
   *
   */
  private void applyResultSet(ResultSet rset) { 
    TimeZone timezone = TimeZone.getDefault();
    GregorianCalendar gc = null;
    java.sql.Clob textClob = null;
    InputStream in = null;
    int b = 0;
    StringBuffer stringBuffer = null;

    try { 
      key = rset.getLong("location_location_id");
      name = rset.getString("location_name"); 
      regionId = rset.getLong("location_region_id"); 

      isDirty = false;
    }
    catch (Exception exp) {
      log("Location.applyResultSet(): SQLException:" + exp.getMessage());
    }

  }


  /**
   *
   */
  private long insertToDB(Connection conn) throws Exception { 
    long newKey = 0;
    String sqlStmt = "";
    ByteArrayInputStream bs = null;
    InputStream in = null;
    Statement stmt = null;
    ResultSet rset = null;

    sqlStmt = "select location_seq.nextval from dual \n";
    stmt = conn.createStatement();
    rset = stmt.executeQuery(sqlStmt);
    rset.next();
    newKey = rset.getLong(1);
    log("newKey=" + newKey);

    sqlStmt = "insert into location  " + 
    " ( " + 
    " location_id, \n" +                      
    " name,     \n" +
    " region_id    \n" +
    "   ) " + 
    " values ( ?,  ?, ? )";
    log("Sql stmt=" + sqlStmt);
    PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
    pStmt.setLong(1,newKey);
    pStmt.setString(2,name);
    pStmt.setLong(3,regionId);

    int rowsInserted = pStmt.executeUpdate();
    log("      rows inserted =" + rowsInserted);
    if ( rowsInserted != 1) { 
      log("Error:  .insertToDB failed no rows inserted");
    }
    pStmt.close();

    isDirty = false;

    return newKey;

  }


  /**
   *
   */
  private void updateOnDB(Connection conn) throws Exception { 

    ByteArrayInputStream bs = null;
    InputStream in = null;
    String sqlStmt = "update location  " + 
    " set " + 
    " name = ?,     \n" +
    " region_id = ?    \n" +
    " where location_id = '" + key + "'";

    log("Sql stmt=" + sqlStmt);
    PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
    pStmt.setString(1,name);
    pStmt.setLong(2,regionId);

    int rowsUpdated = pStmt.executeUpdate();
    if ( rowsUpdated != 1) { 
      log("Error:  .insertToDB failed no rows inserted");
    }
    pStmt.close();
    isDirty = false;

  }


  /**
   *
   * Delete an Location 
   * Also call delete() on any dependent objects.
   * If no Connection was passed in then we are responisible for beginning the transaction
   * Turn off auto commit, and rollback if we get exception from this or any dependent objects
   *
   */
  public void delete() throws Exception { 
    long returnKey = key;
    Connection conn = getConnection();
    try { 
      conn.setAutoCommit(false);  
      delete(conn);
      conn.commit();              
    }
    catch (Exception exp) {    //Any exception thrown by store causes a rollback. 
      log("Location.delete(), Exception: " + exp.getMessage());
      try {
        conn.rollback(); 
      } catch (SQLException rollbackExp) {             
        log(".delete(), Exception: " + rollbackExp.getMessage());
      } 
      throw exp;  //Throw exception back up so that user interface can report error
    }
    finally { 
      try {
        conn.close();
      } catch (SQLException closeExp) {             
        log("Location.delete(), Exception: " + closeExp.getMessage());
      } 
    }
  }


  /**
   *
   *
   */
  public void delete(Connection conn) throws Exception { 
    String sqlStmt = "delete from location " + 
    "where location_id = ?";
    PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
    pStmt.setLong(1,key);
    int rowsDeleted = pStmt.executeUpdate(); 
    log("   Deleted row from location where primary key was " + key);
    if ( rowsDeleted != 1) { 
      log("Error:  Location.delete failed no rows deleted");
     }
  }


  public long getKey() { return key; } 

  public String getName() { 
    return name;
  }


  public long getRegionId() { 
    return regionId;
  }


  public void setName(String in) { 
    name = in;
    isDirty = true;
  }


  public void setRegionId(long in) { 
    regionId = in;
    isDirty = true;
  }



  //  Getter Methods for properties in Region

  public String getRegionName() { 
    return region.getRegionName();
  }

  //  Getter Methods for properties in Division

  public String getDivisionName() { 
    return division.getDivisionName();
  }

  /**
   *  Write to Log
   */
  private void log(String messageIn) { 
    System.out.println(messageIn);
  }


  /**
   *  Get JDBC Connection to the DataBase 
   *  Note: this should be returning connections from a pool.
   */
  private Connection getConnection() { 

    Connection conn = null;

    String dbServer   =  "@myserver.mycompnay.com";
    String dbPort     =  "1526";
    String dbSid      =  "mysid";
    String dbUserId   =  "username";
    String dbPassword =  "password";
    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      String connectString = "jdbc:oracle:thin:" + dbServer + ":" + dbPort + ":" + dbSid;
      conn = DriverManager.getConnection (connectString,  dbUserId, dbPassword);
    }
    catch (SQLException exp) {
      log("Exception: " + exp.getMessage() );
    }
    return conn;
  }


  /**
   *  A main method for testing
   */
  public static void main(String args[] ) { 
    Location object = new Location(1);
    System.out.println("name = " + object.name );
    System.out.println("regionId = " + object.regionId );

  }


}