/*
put this in .../MyWebAppFolder/WEB-INF/classes/src/com/banking

to compile:
cd .../MyWebAppFolder/WEB-INF/classes/src
javac -classpath ..:/usr/local/tomcat/common/lib/servlet.jar com/banking/BankServlet.java -d ..



*/
package com.banking;

import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;


public class BankService{
  java.sql.Connection conn = null;

  public BankService() throws Exception{
      Class.forName("org.gjt.mm.mysql.Driver").newInstance();
      String connurl = 
	"jdbc:mysql://localhost.localdomain/accounts";
      String dbuser = "DBUSRENAME";
      String dbpw = "DBPASSWORD";
      conn = DriverManager.getConnection(connurl,dbuser,dbpw);
      if(conn == null){
	throw new BankingException("Can't make DB Connection");
      }
  }

  public int getUserID(String cardNo, String pin) throws BankingException, SQLException{
    Statement stmt = conn.createStatement();
    String qry = "select user_id from customer where card_number='" + 
      cardNo + "' and pin='" + pin + "';";
    ResultSet res = stmt.executeQuery(qry);
    res.first();
    if(res.isAfterLast() || res.isBeforeFirst()){
      throw new BankingException("Incorrect card number or pin.");
    }else{
      return res.getInt("user_id");
    }
  }


  public Account[] getAccounts(int userid) throws BankingException, SQLException{
    Statement stmt = conn.createStatement();
    String qry = "select account_name, balance from accounts where user_id=" +
      userid + ";";
    ResultSet res = stmt.executeQuery(qry);

    res.last();
    int nrows = res.getRow();
    
    if(nrows <= 0){
      throw new BankingException("Customer has no accounts.");
    }

    Account[] ans = new Account[nrows];
    int i=0;
    for(res.first();!res.isAfterLast();res.next()){
      ans[i++] = new Account(res.getString("account_name"), 
			     res.getInt("balance"));
    }
    return ans;
  }


  private int getBalance(int userid, String accountName) throws BankingException, SQLException{
    Statement stmt = conn.createStatement();
    String qry = "select balance from accounts where user_id=" + userid + 
      " and account_name='" + accountName + "';";
    ResultSet res = stmt.executeQuery(qry);
    res.first();
    int tmp;
    try{
      tmp = res.getInt("balance");
    }catch(SQLException sqle){
      throw new BankingException("Customer does not have account: " + 
				 accountName + " (" + sqle + ")");
    }
    return tmp;
  }


  private void setBalance(int userid, String accountName, int amount) throws SQLException{
    Statement stmt = conn.createStatement();
    String qry = "update accounts set balance=" + amount + 
      " where user_id=" + userid + " and account_name='" +accountName + "';";
    ResultSet res = stmt.executeQuery(qry);
  }
  
  public void transferFunds(int userid, String fromAcctName, String toAcctName, int transferAmount) throws BankingException, SQLException{

    int fromBal = getBalance(userid, fromAcctName);
    int toBal = getBalance(userid, toAcctName);
    if(fromBal < transferAmount){
      throw new BankingException("Insufficient funds for transfer.");
    }

    fromBal -= transferAmount;
    toBal += transferAmount;

    setBalance(userid, fromAcctName, fromBal);
    setBalance(userid, toAcctName, toBal);
  }
}

/* (c) 2003 Prentice Hall PTR . */