/*

 *

 * 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