/*
 *
 * Copyright 2000 Sun Microsystems, Inc. All Rights Reserved.
 * 
 * This software is the proprietary information of Sun Microsystems, Inc.  
 * Use is subject to license terms.
 * 
 */

import java.util.*;
import javax.ejb.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import javax.transaction.*;

public class TellerEJB implements SessionBean {

   private String customerId;
   private double machineBalance;
   private SessionContext context;
   private Connection con;
   private String dbName = "java:comp/env/jdbc/TellerDB";

   public void withdrawCash(double amount) {

      UserTransaction ut = context.getUserTransaction();

      try {
         ut.begin();
         updateChecking(amount);
         machineBalance -= amount;
         insertMachine(machineBalance);
         ut.commit();
      } catch (Exception ex) {
          try {
             ut.rollback();
          } catch (SystemException syex) {
              throw new EJBException
                 ("Rollback failed: " + syex.getMessage());
          }
          throw new EJBException 
             ("Transaction failed: " + ex.getMessage());
     }
   }

   public double getCheckingBalance() {

      try {
         return selectChecking();
      } catch (SQLException ex) {
          throw new EJBException
             ("Unable to get balance: "
             + ex.getMessage());
      }
   }

   public void ejbCreate(String id) throws CreateException {

      customerId = id;

      try {
         makeConnection();
         machineBalance = selectMachine();
      } catch (Exception ex) {
          throw new CreateException(ex.getMessage());
      }
     
   }

   public void ejbRemove()  {

      try {
         con.close();
      } catch (SQLException ex) {
          throw new EJBException(ex.getMessage());
      }
   }

   public void ejbActivate()  {

      try {
         makeConnection();
      } catch (Exception ex) {
          throw new EJBException(ex.getMessage());
      }
   }

   public void ejbPassivate()  {

      try {
         con.close();
      } catch (SQLException ex) {
          throw new EJBException(ex.getMessage());
      }
   }

   public void setSessionContext(SessionContext context) {
      this.context = context;
   }

   public TellerEJB() {}

/************************** Database Routines **********************/

   private void makeConnection() 
      throws NamingException, SQLException {

      InitialContext ic = new InitialContext();
      DataSource ds = (DataSource) ic.lookup(dbName);
      con =  ds.getConnection();
   }

   private void updateChecking(double amount) 
      throws SQLException {

      String updateStatement =
            "update checking set balance =  balance - ? " +
            "where id = ?";

      PreparedStatement prepStmt = 
            con.prepareStatement(updateStatement);

      prepStmt.setDouble(1, amount);
      prepStmt.setString(2, customerId);
      prepStmt.executeUpdate();
      prepStmt.close();
   }

   private void insertMachine(double amount)
      throws SQLException {

      String insertStatement =
            "insert into cash_in_machine values " +
            "( ? , current_date )";

      PreparedStatement prepStmt =
            con.prepareStatement(insertStatement);

      prepStmt.setDouble(1, amount);
      prepStmt.executeUpdate();
      prepStmt.close();
   }

   private double selectMachine() throws SQLException  {

      String selectStatement =
            "select amount " +
            "from cash_in_machine " +
            "where time_stamp = " +
            "(select max(time_stamp) from cash_in_machine)";
      PreparedStatement prepStmt =
            con.prepareStatement(selectStatement);

      ResultSet rs = prepStmt.executeQuery();

      if (rs.next()) {
         double result = rs.getDouble(1);
         prepStmt.close();
         return result;
      }
      else {
         prepStmt.close();
         throw new EJBException
           ("Row for id " + customerId + " not found.");
      } 
   }

   private double selectChecking() throws SQLException  {

      String selectStatement =
            "select balance " +
            "from checking " +
            "where id = ?";
      PreparedStatement prepStmt =
            con.prepareStatement(selectStatement);

      prepStmt.setString(1, customerId);
      ResultSet rs = prepStmt.executeQuery();

      if (rs.next()) {
         double result = rs.getDouble(1);
         prepStmt.close();
         return result;
      }
      else {
         prepStmt.close();
         throw new EJBException
           ("Row for id " + customerId + " not found.");
      } 
   }

} // TellerEJB