package myBean;

import java.sql.*;
import java.util.*;
import java.io.*;
import myBean.*;
/**
 *ProductBean包含和Product表相關的操作
 */
public class BookBean
{
	private Connection con;
	//構造方法，獲得數據庫的連接。
	public BookBean()
	{
		this.con=DBConnection.getConnection();
	}
	/**
	 *搜索所有的商品信息。
	 *返回由Product值對象組成的Collection
	 */
	public Collection getAllBook()throws Exception
	{
		Statement stmt=con.createStatement();
		ResultSet rst=stmt.executeQuery("select * from Books");
		Collection ret=new ArrayList();
		while(rst.next())
		{
			Book temp=new Book();
			temp.setBookId(rst.getString("bookId"));
			temp.setTypeName(rst.getString("typeName"));
			temp.setName(rst.getString("name"));
			temp.setWriter(rst.getString("writer"));
			temp.setPublisher(rst.getString("publisher"));
			temp.setDay(rst.getFloat("day"));
			temp.setPrice(rst.getFloat("price"));
			temp.setOnsale(rst.getFloat("onsale"));
            temp.setNewPrice(rst.getFloat("newprice"));
            temp.setDescription(rst.getString("description"));
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	
	/**
	 *按照商品的類別查找商品，
	 *返回由Product值對象組成的Collection
	 */
	public Collection getBookByType(String typeName)throws Exception
	{
		Statement stmt=con.createStatement();
		ResultSet rst=stmt.executeQuery("select * from Books where typeName='"+typeName+"'");
		Collection ret=new ArrayList();
		while(rst.next())
		{
			Book temp=new Book();
			temp.setBookId(rst.getString("bookId"));
			temp.setTypeName(rst.getString("typeName"));
			temp.setName(rst.getString("name"));
			temp.setWriter(rst.getString("writer"));
			temp.setPublisher(rst.getString("publisher"));
			temp.setDay(rst.getFloat("day"));
			temp.setPrice(rst.getFloat("price"));
			temp.setOnsale(rst.getFloat("onsale"));
            temp.setNewPrice(rst.getFloat("newprice"));
            temp.setDescription(rst.getString("description"));
			
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	/**
	 *按照圖書的名稱查找圖書，
	 *返回由Book值對象組成的Collection
	 */
	public Collection getBookByName(String name)throws Exception
	{
		Statement stmt=con.createStatement();
		ResultSet rst=stmt.executeQuery("select * from Books where name='"+name+"'");
		Collection ret=new ArrayList();
		while(rst.next())
		{
			Book temp=new Book();
			temp.setBookId(rst.getString("bookId"));
			temp.setTypeName(rst.getString("typeName"));
			temp.setName(rst.getString("name"));
			temp.setWriter(rst.getString("writer"));
			temp.setPublisher(rst.getString("publisher"));
			temp.setDay(rst.getFloat("day"));
			temp.setPrice(rst.getFloat("price"));
			temp.setOnsale(rst.getFloat("onsale"));
            temp.setNewPrice(rst.getFloat("newprice"));
            temp.setDescription(rst.getString("description"));
			
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	
	/**
	 *按照圖書的作者查找圖書，
	 *返回由Book值對象組成的Collection
	 */
	public Collection getBookByWriter(String writer)throws Exception
	{
		Statement stmt=con.createStatement();
		ResultSet rst=stmt.executeQuery("select * from Books where writer='"+writer+"'");
		Collection ret=new ArrayList();
		while(rst.next())
		{
			Book temp=new Book();
			temp.setBookId(rst.getString("bookId"));
			temp.setTypeName(rst.getString("typeName"));
			temp.setName(rst.getString("name"));
			temp.setWriter(rst.getString("writer"));
			temp.setPublisher(rst.getString("publisher"));
			temp.setDay(rst.getFloat("day"));
			temp.setPrice(rst.getFloat("price"));
			temp.setOnsale(rst.getFloat("onsale"));
            temp.setNewPrice(rst.getFloat("newprice"));
            temp.setDescription(rst.getString("description"));
			
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	
	
	
	/**
	 *添加一個商品，使用Book值對象作為參數傳給這個方法。
	 */
    
		public void addBook(Book book)throws Exception
	{
		
		PreparedStatement	pstmt=con.prepareStatement("insert into Books values(?,?,?,?,?,?,?,?,?,?)");
		
		pstmt.setString(1,book.getBookId());
		pstmt.setString(2,book.getTypeName());
		pstmt.setString(3,book.getName());
		pstmt.setString(4,book.getWriter());
		pstmt.setString(5,book.getPublisher());
		pstmt.setFloat(6,book.getDay());
		pstmt.setFloat(7,book.getPrice());
		pstmt.setFloat(8,book.getOnsale());
		pstmt.setFloat(9,book.getNewPrice());
		pstmt.setString(10,book.getDescription());
		
		
		pstmt.execute();
		
	}

	/**
	 *更改商品的信息，使用Product值對象作為參數傳給這個方法。
	 */
	public void modifyBook(Book book)throws Exception
	{
		
		PreparedStatement	pstmt=con.prepareStatement("update Books set typeName=?,name=?, writer=?,publisher=?,day=?,price=?,onsale=?,newprice=?,description=? where bookId=?");
	   
	    pstmt.setString(1,book.getBookId());
		pstmt.setString(2,book.getTypeName());
		pstmt.setString(3,book.getName());
		pstmt.setString(4,book.getWriter());
		pstmt.setString(5,book.getPublisher());
		pstmt.setFloat(6,book.getDay());
		pstmt.setFloat(7,book.getPrice());
		pstmt.setFloat(8,book.getOnsale());
		pstmt.setFloat(9,book.getNewPrice());
		pstmt.setString(10,book.getDescription());
		
		
		pstmt.execute();		
	}
	
	/**
	 *刪除一個商品，指定商品的ID
	 */
	public void deleteBook(String bookId)throws Exception
	{
		Statement stmt=con.createStatement();
		stmt.execute("delete from Books where bookId='"+bookId+"'");
	}
	
	/**
	 *返回給定ProductId的商品的信息，
	 *返回的是值對象
	 */
	public Book getBookInfo(String bookId)throws Exception
	{
		
		Statement stmt=con.createStatement();
		ResultSet rst=stmt.executeQuery("select * from Books where bookId='"+bookId+"'");
		Book book=null;
		while(rst.next())
		{
			book=new Book();
			book.setBookId(rst.getString("bookId"));
			book.setTypeName(rst.getString("typeName"));
			book.setName(rst.getString("name"));
			book.setWriter(rst.getString("writer"));
			book.setPublisher(rst.getString("publisher"));
			book.setDay(rst.getFloat("day"));
			book.setPrice(rst.getFloat("price"));
			book.setOnsale(rst.getFloat("onsale"));
			book.setNewPrice(rst.getFloat("newprice"));
			book.setDescription(rst.getString("description"));
		}
		return book;
	}	
}