package DBAO2;
import BookClass.BookDetail;
import CItemClass.ShoppingCartItem;
import SCartClass.ShoppingCart;
import java.util.*;
import java.sql.*;




 public class BookDBAO2
{
	private ArrayList books=null;
	//private String url="jdbc:odbc:SCHOOL";
	private String DBNum="0";//來自BookDB
	private Connection con=null;	
	//private PreparedStatement prepStmt=null;
	//private ResultSet rs=null;
	
	
	
	//===============================================================
	
	
	
	
	//===============================================================
	
	//構造函數--連接數據庫
	public BookDBAO2()
	{
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		}catch(ClassNotFoundException e){
			System.out.println("加載驅動器異常");
		}
					
	
		try{
			con=DriverManager.getConnection("jdbc:odbc:SCHOOL");
		}catch(SQLException e){
			System.out.println("不能連接數據庫");
		}
	}
	
	
	//關閉連接
	
	public void closedbao()
	{
		try{
			con.close();
		}catch(SQLException ex){
			System.out.println("關閉數據庫異常"+ex.getMessage());
		}
	}
	
	//來自BookDB==============
	public void setDBNum(String DBNum)
	{
		this.DBNum=DBNum;
	}
	public String getDBNum()
	{
		return this.DBNum;
	}
	
	
    //查詢數據庫取得書目列表
     public List getBooks(){ //throws BooksNotFoundException
           books=new ArrayList();
           
           try{
               String selectStatement="select * from Book2";
               PreparedStatement prepStmt=con.prepareStatement(selectStatement);
               ResultSet rs=prepStmt.executeQuery();
               
               while(rs.next()){
                  BookDetail bd=
                      new BookDetail(rs.getString(1),rs.getString(2),
                          rs.getString(3),rs.getFloat(4),
                          rs.getString(5),rs.getInt(6));
                          
                  //if(rs.getInt(6)>0){
                       books.add(bd);
                  //}
                  
                  //以下是用來測試的打印--打印書的信息
                  /*
                  System.out.println(bd.getBDescription());
                  System.out.println(bd.getBName());
                  System.out.println(bd.getBNum());
                  System.out.println(bd.getBPrice());
                  System.out.println(bd.getBStore());
                  System.out.println(bd.getBWriter());*/
              }
              
              prepStmt.close();
              
            }catch(SQLException ex){
              System.out.println("查詢數據庫取得書目列表 異常");
              //throw new BooksNotFoundException(ex.getMessage());
            }
            
            Collections.sort(books);
            
            return books;
     }
     
     
     //得到書的全部信息（屬性）
     public BookDetail getBookDetails(String BNum){ 
       /*throws BookNotFoundException*/
       try{
           String selectStatement="select BNum,BName,BWriter,BPrice,BDescription,BStore"+"from Book2 where BNum=?";
           PreparedStatement prepStmt=con.prepareStatement(selectStatement); 
           prepStmt.setString(1,BNum);
           
           ResultSet rs=prepStmt.executeQuery();
           
           if(rs.next()){
             BookDetail bd=
                      new BookDetail(rs.getString(1),rs.getString(2),
                          rs.getString(3),rs.getFloat(4),
                          rs.getString(5),rs.getInt(6));
             
             prepStmt.close();
             return bd;
       }else{
             prepStmt.close();                 
             //System.out.println("找不到書，書號："+bookId);
       }
     }catch(SQLException ex){
        System.err.println(ex.getMessage());
        //throw new BookNotFoundException("Couldn't find book:"+bookId+
            //""+ex.getMessage());
     }
     return null;
   }
   
   
   //用ShoppingCart買書1
   public void buyBooks(ShoppingCart cart)/*throws OrderException*/{
        Collection items=cart.getItems();
        Iterator i=items.iterator();
        
        try{
            con.setAutoCommit(false);
            
            while(i.hasNext()){
                ShoppingCartItem sci=(ShoppingCartItem)i.next();
                BookDetail bd=(BookDetail)sci.getItem();
                String id=bd.getBNum();
                int quantity=sci.getQuantity();
                buyBook(id,quantity);
            }
            
            con.commit();
            con.setAutoCommit(true);
        }catch(Exception ex){
           try{
               con.rollback();
               /*throw new OrderException("Transaction failed:"+
                   ex.getMessage());*/
             }catch(SQLException sqx){
                 System.out.println("用ShoppingCart買書異常");
             }
        }
    }
    
    
   //買書2 
   public void buyBook(String BNum,int quantity)/*throws OrderException*/{
       try{
           String selectStatement="select BNum,BName,BWriter,BPrice,BDescription,BStore"+"from Book2 where BNum=?";
           PreparedStatement prepStmt=con.prepareStatement(selectStatement); 
           prepStmt.setString(1,BNum);
           
           ResultSet rs=prepStmt.executeQuery();
           
           if(rs.next()){
               int inventory=rs.getInt(6);
               prepStmt.close();
               
               if((inventory-quantity)>=0){
                   String updateStatement=
                        "update Book set BStore=inventory-?where BNum=?";
                   prepStmt=con.prepareStatement(updateStatement);     
                   prepStmt.setInt(1,quantity);
                   prepStmt.setString(2,BNum);
                   prepStmt.executeUpdate();
                   prepStmt.close();
               }else{
                  //throw new OrderException("Not enough of"+bookId+
                    //  "in stock to complete order.");
               }
           }
   }catch(SQLException ex){
       System.out.println("買書代碼塊出錯");
   }
  }
  
 /*
  public static void main(String []args)
  {
  	BookDBAO dbao=new BookDBAO();
    dbao.getBooks();
  }
  */
  
}