/** access mysql database through datasource */
package mypack;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
import java.util.*;

public class Cdstore {

  private ArrayList cd;
  private DataSource ds=null;
  

  public Cdstore () throws Exception{

     Context ctx = new InitialContext();
     if(ctx == null )
         throw new Exception("No Context");
     ds =(DataSource)ctx.lookup("java:comp/env/jdbc/cdstore");

  }

  public Connection getConnection()throws Exception{
      return ds.getConnection();
  }

  public void closeConnection(Connection con){
    try{
        if(con!=null) con.close();
      }catch(Exception e){
        e.printStackTrace();
      }
  }

  public void closePrepStmt(PreparedStatement prepStmt){
    try{
        if(prepStmt!=null) prepStmt.close();
      }catch(Exception e){
        e.printStackTrace();
      }
  }

  public void closeResultSet(ResultSet rs){
    try{
        if(rs!=null) rs.close();
      }catch(Exception e){
        e.printStackTrace();
      }
  }

  public int getNumberOfCds() throws Exception {
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs=null;
    cd = new ArrayList();

    try {
      con=getConnection();
      String selectStatement = "select * " + "from cd";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {
        CdDetails bd = new CdDetails(rs.getString(1), rs.getString(2), rs.getString(3),
         rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7), rs.getString(8),rs.getInt(9));
        cd.add(bd);
      }

    }catch (Exception e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }
    return cd.size();
  }

  public Collection getCds()throws Exception{
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    cd = new ArrayList();
    try {
      con=getConnection();
      String selectStatement = "select * " + "from cd";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {

        CdDetails bd = new CdDetails(rs.getString(1), rs.getString(2), rs.getString(3),
           rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7), rs.getString(8),rs.getInt(9));
        cd.add(bd);
      }
     
    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

    Collections.sort(cd);
    return cd;
  }
  
  
    public Collection gettop10()throws Exception{
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    cd = new ArrayList();
    int i=0;
    try {
      con=getConnection();
      String selectStatement = "select * " + "from cd order by saleAmount desc";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()&&(i<10)) {
        i++;
        CdDetails bd = new CdDetails(rs.getString(1), rs.getString(2), rs.getString(3),
           rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7), rs.getString(8),rs.getInt(9));
        cd.add(bd);
      }
     
    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

  
    return cd;
  }
  
 public Collection getUser()throws Exception{
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    cd = new ArrayList();
    try {
      con=getConnection();
      String selectStatement = "select * " + "from Customer";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {

        Customer bd = new Customer(rs.getString(1), rs.getString(2), rs.getInt(3),rs.getString(4), rs.getString(5));
        cd.add(bd);
      }
     
    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

  
    return cd;
  }
  
 public Collection getCdlack()throws Exception{
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    cd = new ArrayList();
    try {
      con=getConnection();
      String selectStatement = "select * " + "from cd where warehouse<150 ";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {

        CdDetails bd = new CdDetails(rs.getString(1), rs.getString(2), rs.getString(3),
           rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7), rs.getString(8),rs.getInt(9));
        cd.add(bd);
      }
     
    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

    Collections.sort(cd);
    return cd;
  }
  
   public Collection getOrder()throws Exception{
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    cd = new ArrayList();
    try {
      con=getConnection();
      String selectStatement = "select * " + "from cdorder  ";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {

        Order bd = new Order(rs.getInt(1), rs.getString(2), rs.getString(3),rs.getString(4), rs.getString(5), rs.getString(6)
           );
        cd.add(bd);
      }
     
    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

   
    return cd;
  }
  
 public Collection getAccount()throws Exception{
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    cd = new ArrayList();
    try {
      con=getConnection();
      String selectStatement = "select * " + "from account"  ;
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {

      Account bd = new Account(rs.getInt(1), rs.getString(2), rs.getString(3),rs.getString(4), rs.getString(5)
           );
        cd.add(bd);
      }
     
    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

   
    return cd;
  }
  
  
    public Collection getGames()throws Exception{
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    cd = new ArrayList();
    try {
      con=getConnection();
      String selectStatement = "select * " + "from cd where saletype='game'";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {

        CdDetails bd = new CdDetails(rs.getString(1), rs.getString(2), rs.getString(3),
           rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7), rs.getString(8),rs.getInt(9));
        cd.add(bd);
      }

    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

    Collections.sort(cd);
    return cd;
  }
  
  public Collection getMovies()throws Exception{
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    cd = new ArrayList();
    try {
      con=getConnection();
      String selectStatement = "select * " + "from cd where saletype='movie'";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {

        CdDetails bd = new CdDetails(rs.getString(1), rs.getString(2), rs.getString(3),
           rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7), rs.getString(8),rs.getInt(9));
        cd.add(bd);
      }

    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

    Collections.sort(cd);
    return cd;
  }
  
  
  public Collection getPrograms()throws Exception{
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    cd = new ArrayList();
    try {
      con=getConnection();
      String selectStatement = "select * " + "from cd where saletype='program'";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {

        CdDetails bd = new CdDetails(rs.getString(1), rs.getString(2), rs.getString(3),
           rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7), rs.getString(8),rs.getInt(9));
        cd.add(bd);
      }

    }catch (SQLException e) 
{
			e.printStackTrace();
}finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

    Collections.sort(cd);
    return cd;
  }

  public CdDetails getCdDetails(String cdId) throws Exception {
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    try {
      con=getConnection();
      String selectStatement = "select * " + "from cd where id = ? ";
      prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, cdId);
      rs = prepStmt.executeQuery();

      if (rs.next()) {
        CdDetails bd = new CdDetails(rs.getString(1), rs.getString(2), rs.getString(3),
          rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7), rs.getString(8),rs.getInt(9));
        prepStmt.close();

        return bd;
      }
      else {
        return null;
      }
    }finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }
  }
    public CdDetails getCdDetailsbyTitle(String title) throws Exception {
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    try {
      con=getConnection();
      String selectStatement = "select * " + "from cd where title = ? ";
      prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, title);
      rs = prepStmt.executeQuery();

      if (rs.next()) {
        CdDetails bd = new CdDetails(rs.getString(1), rs.getString(2), rs.getString(3),
          rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7), rs.getString(8),rs.getInt(9));
        prepStmt.close();

        return bd;
      }
      else {
        return null;
      }
    }finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }
  }
  
  public Customer getUserDetails(String cdId) throws Exception {
    Connection con=null;